Sumx weakness

[za-qhawem]

Hi Guys.

I have built a report on Power BI desktop using data that sits in analysis services. The measure in services takes total packs sold, multiplies it by the units within the pack, then divides that number by a standard pack size. I have different pack sizes and this calculation allows me to get everything to a standard pack size which I use for accurate comparison.

Example

Milk 1 sold 200 packs, each pack has 1.5 litres of milk. Milk 2 sold 170 packs but each pack has 2 litres of milk. Standard pack size is 1.5 litres

Measure for Milk 1: 200 * 1.5 = 300/1.5 = 200 packs

Measure for Milk 2: 170 * 2 = 340/1.5 = 227 packs

So although milk 2 sold fewer packs (in original form), when converted to an "equivalent" pack size, we can see that Milk 2 is selling more packs.

When I use this measure on Power BI desktop the output at a row level is 100% correct but when I aggregate into a category, the calculation changes form. It aggregates each variable first, then applies the the above measure. It changes the output completely.

As a work around, I am applying the sumx function on Power BI desktop to calculate row totals and aggregate. This result is 100% correct.

Problem

My sumx work around gives me the correct output however I have over 10 million rows in my dataset. Every time I change the view/filter the measure in services calculates that view, then the sumx function also runs through it's calculation resulting in a very inefficience report experience.

Does anyone have a solution/substitute to give me the same output with a more efficient experience.

Thank you!

[AlB]

@za-qhawem 

I'd need the code for the measure [Total case equivalent], in text rather than in a screen cap, and like I said a sample of the relevant tables in the model

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB]

Hi @za-qhawem 

Can you show the relevant pieces of code and a sample of the relevant tables in the model?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[za-qhawem]

Hi @AlB 

Thanks for getting back to me.

Thats a screen shot of the final sumx measure. The case equivalent measure sits within analysis services.

I hope that clarifies more?