cancel
Showing results for
Did you mean:
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

 Month City State Country UniqueSubjectID Impacted_Flag 2022-9 City_A State_A Country_A 134 Y 2022-9 City_A State_A Country_A 133 N 2022-9 City_A State_A Country_A 132 N 2022-9 City_A2 State_A Country_A 131 N 2022-9 City_A2 State_A Country_A 130 Y 2022-9 City_A2 State_A Country_A 129 Y 2022-9 City_C1 State_B Country_A 128 Y 2022-9 City_C1 State_B Country_A 127 Y 2022-9 City_C1 State_B Country_A 126 N 2022-9 City_C2 State_B Country_A 125 N 2022-9 City_C2 State_B Country_A 124 N 2022-9 City_C2 State_B Country_A 123 Y 2022-8 City_A State_A Country_A 122 N 2022-8 City_A State_A Country_A 121 Y 2022-8 City_A State_A Country_A 120 Y 2022-8 City_A2 State_A Country_A 119 N 2022-8 City_A2 State_A Country_A 118 N 2022-8 City_A2 State_A Country_A 117 Y 2022-8 City_C1 State_B Country_A 116 Y 2022-8 City_C1 State_B Country_A 115 N 2022-8 City_C1 State_B Country_A 114 N 2022-8 City_C2 State_B Country_A 113 Y 2022-8 City_C2 State_B Country_A 112 Y 2022-8 City_C2 State_B Country_A 111 N

Using Matrix I can display the below:

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.

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
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.

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)

Frequent Visitor

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:

Expected:

Frequent Visitor

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!

Announcements

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

Power BI March 2023 Update

Find out more about the March 2023 update.

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors