Forum Discussion
Getting percent on row while counting some columns
I have a situation where I have a table with individual feedback scores and want to produce a matrix showing rating groups and their percentage of the year's total feedback but I don't want to add counts for a 0 score (meaning the question wasn't answered). I have looked at quite a few examples that seem similar but not quite the same. From looking at examples in this community, I have tried a few things I've seen and I have it close to correct but I think I am not doing it right.
| Date | Customer | Feedback score |
| 12/02/2019 | ABC | 5 |
| 12/05/2019 | SDF | 7 |
| 04/20/2020 | FDR | 0 |
| 05/21/2020 | GTR | 6 |
| 06/12/2020 | EWR | 2 |
| 07/23/2021 | QWE | 7 |
| 08/21/2021 | FDS | 1 |
| 06/25/2022 | ABC | 0 |
| 07/12/2022 | HJK | 5 |
this would then consolidate to (counts shown):
| Year | 0 | 1-5 | 6-7 | Total |
| 2019 | 0 | 1 | 1 | 2 |
| 2020 | 1 | 1 | 1 | 3 |
| 2021 | 0 | 1 | 1 | 2 |
| 2022 | 1 | 1 | 0 | 2 |
What I would like to add is a percentage for the rating groups of 1-5 and 6-7 of their amounts of the total that doesn't include counts in the 0 column. For example, the first row both 1-5 and 6-7 would be 50%. 2020 it would also be 50% because the total count would be 2 (don't include the 0 column). 2022 would be 100% for the rating group of 1-5.
I created two measures:
# of non-zero feedbacks =
What it seems to do is correctly show on the very bottom total line (the 83% for 6-7 rating should be 83.3%, so it is probably just format but correct number) but for 2022, it should be 86.7% and not 5% (26 / 30). The 0 column doesn't really need a percentage because it isn't included in response rating percentages - so it really should be 0% and then the 1-5 and 6-7 percentages should be there.
I had laid it out this way because it seemed easy to view the counts and percentages, but it can be changed to show the percentage next to the appropriate columns if making the calculations work is easier.
Can someone help me figure the right measures and how to make the matrix to present this?
Hi Anonymous
I got this!
Percentage Measure = VAR Numerator = CALCULATE( [FeedbackCount], 'Customer Feedback'[feedback score] <> 0 ) VAR Denominator = CALCULATE( [FeedbackCount], CALCULATETABLE( 'Customer Feedback', 'Customer Feedback'[feedback score] <> 0, REMOVEFILTERS('Customer Feedback'[feedback score (groups)]) ) ) RETURN DIVIDE( Numerator, Denominator, BLANK() )Anonymous try another measure...should be something like this (maybe???)
CALCULATE( [The Measure I Gave You Earlier], [Group/bin] = "6-7" )
17 Replies
ok so i stripped out all colummns except a couple but kept the full records so I can figure out what they should be easier. 🙂 I uploaded it to google drive so hopefully this works... https://docs.google.com/spreadsheets/d/1kVmuUrrEvUIbjOnYPh9YOeP4WoE42goL/edit?usp=sharing&ouid=109249049242950252834&rtpof=true&sd=true
I was going to copy up also my trial PBI file but it kept showing a path with some restricted info in it for my data source so I don't think I can upload it - couldn't get it to point to the Google drive file.
For the 6-7 column the percentage by year should be:
2022 = 86.7%
2021 = 84.8%
2020 = 79.8%
2019 = 86.2%
total of all years = 83.3%
The 2-5 (guess I have no 1's lol) would be the difference between 100 and the 6-7 percentages....
I used groups on the feedback score to provide the groupings...
Hi Anonymous
I got this!
Percentage Measure = VAR Numerator = CALCULATE( [FeedbackCount], 'Customer Feedback'[feedback score] <> 0 ) VAR Denominator = CALCULATE( [FeedbackCount], CALCULATETABLE( 'Customer Feedback', 'Customer Feedback'[feedback score] <> 0, REMOVEFILTERS('Customer Feedback'[feedback score (groups)]) ) ) RETURN DIVIDE( Numerator, Denominator, BLANK() )
Hi Anonymous
Try this...
Average Measure = AVERAGEX( FILTER( 'Customer Feedback', 'Customer Feedback'[score] > 0 ), 'Customer Feedback'[score] )Hope this helps!
Anonymous replace