topic Re: Measure not aggregated in Pivot Table in App Development

Measure not aggregated in Pivot Table

RighettiAndrea — Wed, 23 Feb 2022 14:53:42 GMT

Hi everyone,
I have a pivot table with 2 dimensions.

As you can see here, the column "Quantità" can be related to every dimension, while the other one ("Ton") is only related to the Date. I'd like to have only the measure called "Qta / Ton" but I'm not able to get the right value.
What I'd like to see is a calculation based on the measure Quantità divided by the total Ton (49377).
For example I want to see in the first row (ECOGARDA SRL) the result of 228260/49377 = 4.62.

Here the measure Quantità
Sum({Acquisti <MonthDiff={"<12"}>} [Righe.Quantità])

And here the measure Ton
Aggr(Sum({<MonthDiff={"<12"}>} [S_ODL.Lordo Fonderia]/[DT_ARTICOLI.Impronte]*[DT_ARTICOLI.Peso Stampata])/1000,AsOfMonth)

How can I get the new measure "Quantità / Ton" as explained before?

Thanks

Re: Measure not aggregated in Pivot Table

Aurelien_Martinez — Wed, 23 Feb 2022 14:58:39 GMT

Hi,

You can try to add your first dimension in the aggr like :

Sum(Aggr(Sum({<MonthDiff={"<12"}>} [S_ODL.Lordo Fonderia]/[DT_ARTICOLI.Impronte]*[DT_ARTICOLI.Peso Stampata])/1000, AsOfMonth, [Fornitori.Fornitori]))

Aurélien

Re: Measure not aggregated in Pivot Table

RighettiAndrea — Thu, 24 Feb 2022 07:09:17 GMT

Hi,

unfortunately it doesn't do what I'm looking for.

If I write the complete measure this way (here below), the numbers aren't correct

Sum({Acquisti <MonthDiff={"<12"}>} [Righe.Quantità]) /
Sum(Aggr(Sum({<MonthDiff={"<12"}>} [S_ODL.Lordo Fonderia]/[DT_ARTICOLI.Impronte]*[DT_ARTICOLI.Peso Stampata])/1000,AsOfMonth,[Fornitori.Fornitore]))

Re: Measure not aggregated in Pivot Table

MayilVahanan — Thu, 24 Feb 2022 07:22:57 GMT

Might be, try like below

"Quantità / Ton":

Sum({Acquisti <MonthDiff={"<12"}>} [Righe.Quantità]) /

Aggr(Sum(Total {<MonthDiff={"<12"}>} [S_ODL.Lordo Fonderia]/[DT_ARTICOLI.Impronte]*[DT_ARTICOLI.Peso Stampata])/1000,AsOfMonth)

Sum({Acquisti <MonthDiff={"<12"}>} [Righe.Quantità]) /

Aggr(Sum(Total<urDim> {<MonthDiff={"<12"}>} [S_ODL.Lordo Fonderia]/[DT_ARTICOLI.Impronte]*[DT_ARTICOLI.Peso Stampata])/1000,AsOfMonth)

Re: Measure not aggregated in Pivot Table

RighettiAndrea — Thu, 24 Feb 2022 07:37:01 GMT

Hi,

neither this unfortunately.

I'll write my calculated dimension here below (I don't know if this can help)...the other one is not calculated.

=If(Year(AsOfMonth)>Year(Now())-3,AsOfMonth)

I'll show you what I get with both the solutions you wrote.

In the first case I don't get the column Fornitori.Fornitore and the Total show is uncorrect.

In the second case, replacing <urDim> with AsOfMonth I don't get the column Fornitori.Fornitore but the Total is correct.

thanks a lot

Re: Measure not aggregated in Pivot Table

RighettiAndrea — Thu, 24 Feb 2022 08:15:44 GMT

Hi everyone!

I've found a soulution.

Sum({Acquisti <MonthDiff={"<12"}>} [Righe.Quantità]) /
Aggr((Sum(Total <AsOfMonth> {<MonthDiff={"<12"}>} [S_ODL.Lordo Fonderia]/[DT_ARTICOLI.Impronte]*[DT_ARTICOLI.Peso Stampata])/1000),AsOfMonth,Fornitori.Fornitore)

It is based on Mayil's answer.

Thanks a lot!!