Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
@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% |
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
Thanks so much, this is exactly what I needed 🙂
@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?
User | Count |
---|---|
139 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |