Calculate then Aggregate

[Johnny]

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.

[PavelR]

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

Regards.

Pavel

[Johnny]

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:

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

[v-yulgu-msft]

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

[PavelR]

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