Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Measure not aggregated in Pivot Table

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

Labels (1)
• ### General Question

1 Solution

Accepted Solutions
Contributor III
Author

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

5 Replies
Partner - Specialist II

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

Help users find answers! Don't forget to mark a solution that worked for you!
Contributor III
Author

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]))`

MVP

Hi

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)

or

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)

Thanks & Regards, Mayil Vahanan R
Contributor III
Author

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

Contributor III
Author

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