cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

v-yulgu-msft Super Contributor
Super Contributor

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 284 members 2,673 guests
Please welcome our newest community members: