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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
onedayover
Helper II
Helper II

Percentages on unpivoted table by group

Hi All

I'm hoping someone can help me out with this problem I'm having. I've tried searching for an answer but still can't work out how to do it correctly.

I have an unpivoted table and am having trouble calculating a percentage for each group.

In the example below, I need a measure that will return the percentage of "True" responses for each separate "Question_No" (Q5-1).

I'm trying to present the associated "Question_Label" with the percentage next to it in a matrix.

Many thanks

Darlene

UnpivotPercentage.PNGUnpivotPercentage2.PNG

1 ACCEPTED SOLUTION

Hi @onedayover,

You can use the following measure formula with your label field to achieve your requirement:

Measure = 
CALCULATE (
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Value] = TRUE () )
        / COUNTROWS ( 'Table' ),
    ALLSELECTED ( 'Table' ),
    VALUES ( 'Table'[Label] )
)

1.png
Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
onedayover
Helper II
Helper II

@amitchandak, hi and thank you for your help. I tried this solution but I wasn't able to get it to work so I'm assuming I explained this wrong. I will place a table of my sample data below and the expected results. 

Thanks again

 

StaffLabelValueQuestion_No
CraigMental wellbeingTRUEQ5-1
HarryMental wellbeingTRUEQ5-1
JackMental wellbeingTRUEQ5-1
JaneMental wellbeingFALSEQ5-1
MichelleMental wellbeingTRUEQ5-1
CraigFamily life improvementTRUEQ5-2
HarryFamily life improvementTRUEQ5-2
JackFamily life improvementTRUEQ5-2
JaneFamily life improvementFALSEQ5-2
MichelleFamily life improvementTRUEQ5-2
CraigReduced stressFALSEQ5-3
HarryReduced stressFALSEQ5-3
JackReduced stressTRUEQ5-3
JaneReduced stressTRUEQ5-3
MichelleReduced stressTRUEQ5-3
CraigTaking regular breaksFALSEQ6-1
HarryTaking regular breaksFALSEQ6-1
JackTaking regular breaksFALSEQ6-1
JaneTaking regular breaksTRUEQ6-1
MichelleTaking regular breaksTRUEQ6-1
CraigCreating a hard line between work and homeTRUEQ6-2
HarryCreating a hard line between work and homeFALSEQ6-2
JackCreating a hard line between work and homeFALSEQ6-2
JaneCreating a hard line between work and homeFALSEQ6-2
MichelleCreating a hard line between work and homeFALSEQ6-2
CraigMaintaining a healthy work/life balanceTRUEQ6-3
HarryMaintaining a healthy work/life balanceTRUEQ6-3
JackMaintaining a healthy work/life balanceTRUEQ6-3
JaneMaintaining a healthy work/life balanceTRUEQ6-3
MichelleMaintaining a healthy work/life balanceTRUEQ6-3

 

Expected results...

Label%
Mental wellbeing80%
Family life improvement80%
Reduced stress60%
Taking regular breaks40%
Creating a hard line between work and home20%
Maintaining a healthy work/life balance100%

Hi @onedayover,

You can use the following measure formula with your label field to achieve your requirement:

Measure = 
CALCULATE (
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Value] = TRUE () )
        / COUNTROWS ( 'Table' ),
    ALLSELECTED ( 'Table' ),
    VALUES ( 'Table'[Label] )
)

1.png
Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks so much, this is exactly what I needed 🙂

amitchandak
Super User
Super User

@onedayover , Try if these measures can help

calculate( divide(calculate(count(table[value]), filter(Table,Table[value] ="True")) , count(table[value])) , allexcept(Table, Table[Question_no]))

calculate( divide(calculate(count(table[value]), filter(Table,Table[value] ="True")) , count(table[value])) , Table[Question_no] ="Q5-1" )

 

if not

Can you share sample data and sample output in table format?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.