cancel
Showing results for
Did you mean:

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

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

#### New forum boards available in Real-Time Intelligence.

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors