Forum Discussion
Power BI Matrix Aggregation Error
- 7 years ago
So first of all, there is some weirdness with your data. For example, Store # 5103 and Store # 5086 don't have entries for all the metrics. Because they have zeroes for all metrics, it looks fine, but I bet if they had metric data, those percentages would look weird.
On to your issue with the RSC score not working for all stores. It actually doesn't work well when you have multiple stores selected. What's happening is that it's calculating the total possible score correctly, but the numerator is summing all of the 'Fact Data'[V3] entries. The issue is that a 1 in the V3 column means a positive result, but doesn't indicate HOW positive the result is. So for example, if one store had a Volunteer Hours score of 1,000,000%, it would drag the percentage you see in the box WAY up, but the RSC score would barely change, since V3 is still a 1.
What you want is a count where [Variance] is positive for May divided by the number of metrics. First things first, your expression for [Variance] is a nightmare. I simplified it to this:
Variance = DIVIDE(SUM('Fact Data'[Absolute]),SUM('Fact Data'[Divisor]), 0)So at this point, there are a few ways to do this. You could figure out some way to use math to count positive values of [Absolute] vs non-zero [Divisor] values, but that sounds like a massive headache. Why not just straight up count the positive results?
RSC Score2 = COUNTROWS(FILTER(SUMMARIZE('Fact Data','Fact Data'[Measure1], "Metric Variance", [Variance]), [Metric Variance]>0)) / [Distinct Measure Count]So this works, and I was playing with it to confirm, and I noticed something really weird about your matrix. I'm not sure why or how, but it's somehow calculating values differently than it should. I've gone and made a matrix with the EXACT same rows/columns/values and it comes up with different results. Even copy/pasting your matrix makes it recalculate all the rows to match my manual calculations.
Note that I DID have to set a visual filter on the New & Improved Score visual to only use data from May.
As far as I can tell by manually going through your Fact Data table, the matrix on the right has the correct value. I'm not sure why the matrix on the left calculates differently, but the New&Improved Score matches the data on the right and my attempts to manually calculate [Variance].
Hopefully that helps. If you have further questions, feel free to ask.
So first of all, there is some weirdness with your data. For example, Store # 5103 and Store # 5086 don't have entries for all the metrics. Because they have zeroes for all metrics, it looks fine, but I bet if they had metric data, those percentages would look weird.
On to your issue with the RSC score not working for all stores. It actually doesn't work well when you have multiple stores selected. What's happening is that it's calculating the total possible score correctly, but the numerator is summing all of the 'Fact Data'[V3] entries. The issue is that a 1 in the V3 column means a positive result, but doesn't indicate HOW positive the result is. So for example, if one store had a Volunteer Hours score of 1,000,000%, it would drag the percentage you see in the box WAY up, but the RSC score would barely change, since V3 is still a 1.
What you want is a count where [Variance] is positive for May divided by the number of metrics. First things first, your expression for [Variance] is a nightmare. I simplified it to this:
Variance = DIVIDE(SUM('Fact Data'[Absolute]),SUM('Fact Data'[Divisor]), 0)So at this point, there are a few ways to do this. You could figure out some way to use math to count positive values of [Absolute] vs non-zero [Divisor] values, but that sounds like a massive headache. Why not just straight up count the positive results?
RSC Score2 = COUNTROWS(FILTER(SUMMARIZE('Fact Data','Fact Data'[Measure1], "Metric Variance", [Variance]), [Metric Variance]>0)) / [Distinct Measure Count]So this works, and I was playing with it to confirm, and I noticed something really weird about your matrix. I'm not sure why or how, but it's somehow calculating values differently than it should. I've gone and made a matrix with the EXACT same rows/columns/values and it comes up with different results. Even copy/pasting your matrix makes it recalculate all the rows to match my manual calculations.
Note that I DID have to set a visual filter on the New & Improved Score visual to only use data from May.
As far as I can tell by manually going through your Fact Data table, the matrix on the right has the correct value. I'm not sure why the matrix on the left calculates differently, but the New&Improved Score matches the data on the right and my attempts to manually calculate [Variance].
Hopefully that helps. If you have further questions, feel free to ask.
- Anonymous7 years agoNot applicable
Hi,
Thanks for your time on this, just back in after vacation excuse me for the delay in getting back to you. Looking at your screenshot it looks to be the right solution, I am struggling to replace the model you developed. Would you be able to send a link to the report?
- Cmcmahan7 years agoResident Rockstar
Sure. Just keep in mind the super weird issue I found where literally copy and pasting the matrix you had apparently makes everything calculate differently. I have no idea why this was happening. The rest of the steps I took are straightforward and listed in the previous post.
https://drive.google.com/open?id=1LOUJabitZ-oMg89-H9cMLAqRz2Z9KrzM