topic Re: Aggregation pivot calculation in App Development

Aggregation pivot calculation

Bart_Breekveldt — Sat, 16 Nov 2024 04:15:34 GMT

Good morning,

I've this table below. The portion refers to the batch amount per article. If I multiply the portion by the amount per portion produced, I get the overall amount (dimension Portion*Amount). The PortionAmount is a master measure with the same formula (with the benefit that I can add an aggregation function from the list). I can't use this master measure in my pivot table (on another sheet). If I aggregate this with SUM, the total is very different. The total amount in "PortionAmount", the master measure is the right (total) amount. Why are these two totals different?

How can I get an aggregation with the right total amount, which works in a pivot table as a measure?

Thanks in advance

TableMaster measure

Re: Aggregation pivot calculation

rubenmarin — Mon, 28 Oct 2019 11:23:05 GMT

hi, it looks like each table row has manny records associated, 35700/105 is 340, so 340 rows with the same numbers are counted in the sum.

This is probably an issue in how is created the data model (just probably).

To get the expected total maybe with Sum(Aggr(Portion*Amount, Article, Ingredient, Portion))

Re: Aggregation pivot calculation

Bart_Breekveldt — Mon, 28 Oct 2019 15:35:00 GMT

Thanks for the tip, it was indeed that portion (batch size) should not be summed. But the formula didn't work. I found the function 'Only', which is an aggregation function. I'm now at: SUM(Amount)*Only(Portion). It don't add up if I remove the Portion dimension because of the Only function. How can I turn this Only function in my Sum function to fix this problem?

Re: Aggregation pivot calculation

rubenmarin — Mon, 28 Oct 2019 16:39:47 GMT

Have you tried the expression I post before?:

Sum(Aggr(Portion*Amount, Article, Ingredient, Portion))

or:

Sum(Aggr(Sum(Portion)*Amount, Article, Ingredient, Portion))