Power BI Matrix Aggregation Error

[Anonymous]

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 measure has a value represented by a % variance, which totalled up to give a aggregate score ratio for a particiular score (whatever was selected in the filters). So for instance if a store was > 0% for 8 out of 16 measures, they would score 50%. 

The problem being is when I move up the hierarchy up to area and region, the scoring breaks down. It aggregates all the stores score in an area instead of taking using just the scores for THAT area. I have attached some images to help.

If anyone is willing to help and think they may know a solution then please get in touch!

Kind regards,

LeviIncorrect

Correct

[Cmcmahan · Accepted solution]

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.

[Anonymous]

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?

[Cmcmahan]

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

[Cmcmahan]

It would help us give you a better answer if you could tell us a little bit about how your tables are set up, what measure you're currently using to calculate Score, and what data/math you want PowerBI to use to calculate Score for an area vs for a store.

It's likely you'll want to use something like:

DynamicScore = IF( ISFILTERED('Table'[Store]), <CURRENT STORE SCORE CALCULATION>, <NEW AREA SCORE CALCULATION> )

If you want to do the same for region, a nested if is the way to go.

[Anonymous]

Can I send you my Power BI file?

[Cmcmahan]

Sure. Feel free to upload it to whatever file sharing service you like (google drive, dropbox, onedrive, etc), and share the link here.

[Anonymous]

Thanks, please see the link to the document here.

Thank you so much for your help on this matter.

I think the first message I posted should be self explanatory, but let me know if its not clear?

Thanks again!