Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Power BI Matrix Aggregation Error

Hi All,   This is a little convoluted to explain so bear with me. I have built a retail scorecard, utilising a number of different metrics, within a retail hierarchy (Region > Area > Store). Each m...
  • Cmcmahan's avatar
    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.