Forum Discussion

Advocate II

9 years ago

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

Solution Specialist

9 years ago

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

Regards.

Pavel

Johnny
Advocate II
9 years ago
Hi Pavel,

Thanks for replying! :o)

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
- PavelR
  Solution Specialist
  9 years ago
  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
- v-yulgu-msft
  Microsoft Employee
  9 years ago
  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

Related content