cancel
Showing results for
Did you mean:
Frequent Visitor

Calculate then Aggregate

Hi,

I'm trying to find a way to keep a calculation on a detailed grain and once it's done on this level then aggregate it (SUM) the rest of the way to the top.

The formula is basically a X*Y (different grains though) and this works fine on the lowest level - so far so good.

Now I want to sum these results (eg. a total for all customers) and this results in 0 because the Y on this level is 0 - makes sense to me. Hence I want to CALCULATE (on product and customer level) first then AGGREGATE the calculated result in stead of "just" calculating it on Customer.

My table looks something like this:

 Calculation (X and Y Values are Measures) Customer Product Y Value X Value Current Result Desired Result 1 1 10 1 10 10 2 1 20 2 40 40 3 1 -30 3 -90 -90 1 2 5 4 20 20 2 2 10 5 50 50 3 2 -15 6 -90 -90 1 3 -15 7 -105 -105 2 3 -30 8 -240 -240 3 3 45 9 405 405 Aggregations Customer Product Y Value X Value Current Result Desired Result 1 0 12 0 -75 2 0 15 0 -150 3 0 18 0 225 1 0 6 0 -40 2 0 15 0 -20 3 0 24 0 60

I've tried to use this formula:

SUMX(

SUMMARIZE(

Customer;

Product);

X*Y)

This results in 0 in all levels (both higher and lower grain).

I can find plenty resources explaining how I aggregate then calculate - but not calculate then aggregate.

Hope anyone can help out - it will be greatly appreciated.

Kind regards,

J.

4 REPLIES 4
Highlighted
Established Member

Re: Calculate then Aggregate

Hi @Johnny. It is not really clear to me, how should the result look like?

Regards.

Pavel

Frequent Visitor

Re: Calculate then Aggregate

Hi Pavel,

My bad - if you take the information out in a pivot table I see this:

I have data like this:

 Product Customer Y Value X Value a x 10 2 a y 20 4 a z -30 6 b x 35 1 b y -80 3 b z 45 5 c x -45 9 c y 60 8 c z -15 7

And a measure which is X*Y

This gives me this result in a pivot table for illustration:

 Sum of Result Pivot Product Customer Total a x 20 y 80 z -180 a Total 0 b x 35 y -240 z 225 b Total 0 c x -405 y 480 z -105 c Total 0 Grand Total 0

Product a)

Clearly the 20+80-180 results in -80 and not 0 as above - and I want it to say -80.

One caveat is that I can't calculate the total in a column - as it is a somewhat complex measure and does not provide me with the correct result.

Hope it makes better sense??

Rgds,

J

Established Member

Re: Calculate then Aggregate

I used your data and I have this result:

I just created Total measure: Total = SUMX(Data;Data[X Value]*Data[Y Value])

and I used it as Value in pivot table.

Hope it is what you wanted.

Regards.

Pavel

Microsoft

Re: Calculate then Aggregate

Hi, @Johnny

Based on my test, if I added a matrix visualization to aggregate data, the total value displayed as -80 correctly. So, what is the entire measure for Total like in your scenario? Would you please share your pbix file for more analysis if possible?

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (2,401)