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! 🐵

 

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

Highlighted
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
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 324 members 3,363 guests
Please welcome our newest community members: