cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Johnny Frequent Visitor
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)
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 Established Member
Established Member

Re: Calculate then Aggregate

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

 

Regards.

Pavel

Johnny Frequent Visitor
Frequent Visitor

Re: Calculate then Aggregate

Hi Pavel,

 

Thanks for replying! Smiley Surprised)

 

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 Established Member
Established Member

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
Community Support Team
Community Support Team

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.