cancel
Showing results for
Did you mean:
Regular 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
Solution Supplier

## Re: Calculate then Aggregate

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

Regards.

Pavel

Regular 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

Solution Supplier

## 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

Highlighted
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

#### ‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors