Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |