Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Noobie
Frequent Visitor

Display Dynamic Percentage of Row Category SubTotals for each row (split based on a column value)

Hi suppose I have below table/raw data and I need to display them using Matrix Visuals showing percentage of either N or Y (column Impacted_Flag) on Count of UniqueSubjectID 

 

MonthCityStateCountryUniqueSubjectIDImpacted_Flag
2022-9City_AState_ACountry_A134Y
2022-9City_AState_ACountry_A133N
2022-9City_AState_ACountry_A132N
2022-9City_A2State_ACountry_A131N
2022-9City_A2State_ACountry_A130Y
2022-9City_A2State_ACountry_A129Y
2022-9City_C1State_BCountry_A128Y
2022-9City_C1State_BCountry_A127Y
2022-9City_C1State_BCountry_A126N
2022-9City_C2State_BCountry_A125N
2022-9City_C2State_BCountry_A124N
2022-9City_C2State_BCountry_A123Y
2022-8City_AState_ACountry_A122N
2022-8City_AState_ACountry_A121Y
2022-8City_AState_ACountry_A120Y
2022-8City_A2State_ACountry_A119N
2022-8City_A2State_ACountry_A118N
2022-8City_A2State_ACountry_A117Y
2022-8City_C1State_BCountry_A116Y
2022-8City_C1State_BCountry_A115N
2022-8City_C1State_BCountry_A114N
2022-8City_C2State_BCountry_A113Y
2022-8City_C2State_BCountry_A112Y
2022-8City_C2State_BCountry_A111N

 

Using Matrix I can display the below:

Noobie_1-1666745805045.png

Noobie_3-1666746370274.png

 

But is there a way to display the above like the below pivot table showing percentage instead ( I manually calculated the percentage)? So that, no matter how a viewer drills up or drills down the rows (country/state/city), the percentage remains correct for each row.  Or simply displaying both numerical numbers and percentages side by side is ok as well.

Noobie_2-1666745921107.png

The calculation for Country A (Cell B4) = 6/ (6+6)   OR  B4 = 6/12

The calculation for Country A (Cell C4) = 6/ (6+6)   OR  C4 = 6/12

The calculation for City_A (Cell B6) = 1/ (1+2)   OR  B6 = 1/3

The calculation for City_A (Cell C6) = 2/ (1+2)   OR  C6 = 2/3

 

Thank you so much !!

 

 

4 REPLIES 4
Noobie
Frequent Visitor

Thank you all for this. 
I resorted to other avenue by simplying what I need or what I try to show by counting the presence of a certain value over the count of all values to get the percentage. 

amitchandak
Super User
Super User

@Noobie , A new measure

 

Switch( True(),

isinscope(Table[City]), divide(count(Table[Impacted_Flag]), calculate(count(Table[Impacted_Flag]), removefilters(Table[City]))) ,

isinscope(Table[State]),divide(count(Table[Impacted_Flag]), calculate(count(Table[Impacted_Flag]), allselected(Table))),1)

Hi amitchandak,

 

Thanks, I managed to create the measure now. But I'm not seeing expected percentage. For example. For 2022-8 Month,
Country A should be 0.5 and 0.5 (50% & 50%) and City A should have 0.33 (33%) and 0.67 (67%)

 

Thanks again!

 

 

 

 

Revised Result:

Noobie_0-1666753770822.png

Expected:

Noobie_1-1666754022898.png

 

Hi amitchandak,

 

Thanks so much for the reply. I tried to use your DAX but having syntax error, not sure where the error is. Wold you be able to have a look? 

 

Thanks again mate!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.