Percentages on unpivoted table by group

[onedayover]

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

[onedayover]

@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

Staff	Label	Value	Question_No
Craig	Mental wellbeing	TRUE	Q5-1
Harry	Mental wellbeing	TRUE	Q5-1
Jack	Mental wellbeing	TRUE	Q5-1
Jane	Mental wellbeing	FALSE	Q5-1
Michelle	Mental wellbeing	TRUE	Q5-1
Craig	Family life improvement	TRUE	Q5-2
Harry	Family life improvement	TRUE	Q5-2
Jack	Family life improvement	TRUE	Q5-2
Jane	Family life improvement	FALSE	Q5-2
Michelle	Family life improvement	TRUE	Q5-2
Craig	Reduced stress	FALSE	Q5-3
Harry	Reduced stress	FALSE	Q5-3
Jack	Reduced stress	TRUE	Q5-3
Jane	Reduced stress	TRUE	Q5-3
Michelle	Reduced stress	TRUE	Q5-3
Craig	Taking regular breaks	FALSE	Q6-1
Harry	Taking regular breaks	FALSE	Q6-1
Jack	Taking regular breaks	FALSE	Q6-1
Jane	Taking regular breaks	TRUE	Q6-1
Michelle	Taking regular breaks	TRUE	Q6-1
Craig	Creating a hard line between work and home	TRUE	Q6-2
Harry	Creating a hard line between work and home	FALSE	Q6-2
Jack	Creating a hard line between work and home	FALSE	Q6-2
Jane	Creating a hard line between work and home	FALSE	Q6-2
Michelle	Creating a hard line between work and home	FALSE	Q6-2
Craig	Maintaining a healthy work/life balance	TRUE	Q6-3
Harry	Maintaining a healthy work/life balance	TRUE	Q6-3
Jack	Maintaining a healthy work/life balance	TRUE	Q6-3
Jane	Maintaining a healthy work/life balance	TRUE	Q6-3
Michelle	Maintaining a healthy work/life balance	TRUE	Q6-3

Expected results...

Label	%
Mental wellbeing	80%
Family life improvement	80%
Reduced stress	60%
Taking regular breaks	40%
Creating a hard line between work and home	20%
Maintaining a healthy work/life balance	100%

[v-shex-msft · 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] )
)

Regards,
Xiaoxin Sheng

[onedayover]

Thanks so much, this is exactly what I needed 🙂

[amitchandak]

@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?