cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Johnny
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)
CustomerProductY ValueX ValueCurrent ResultDesired Result
111011010
212024040
31-303-90-90
12542020
221055050
32-156-90-90
13-157-105-105
23-308-240-240
33459405405
      
Aggregations
CustomerProductY ValueX ValueCurrent ResultDesired Result
1 0120-75
2 0150-150
3 0180225
 1060-40
 20150-20
 3024060

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
PavelR Solution Supplier
Solution Supplier

Re: Calculate then Aggregate

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

 

Regards.

Pavel

Johnny
Regular Visitor

Re: Calculate then Aggregate

Hi Pavel,

 

Thanks for replying! 🐵

 

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

 

I have data like this:

ProductCustomerY ValueX Value
ax102
ay204
az-306
bx351
by-803
bz455
cx-459
cy608
cz-157

 

And a measure which is X*Y

 

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

Sum of Result Pivot  
ProductCustomerTotal
ax20
 y80
 z-180
a Total 0
bx35
 y-240
 z225
b Total 0
cx-405
 y480
 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

PavelR Solution Supplier
Solution Supplier

Re: Calculate then Aggregate

I used your data and I have this result:

 

Výstřižek.PNG

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

Helpful resources

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

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

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

April 2020 Community Highlights

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

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors