Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
RighettiAndrea
Contributor III
Contributor III

Measure not aggregated in Pivot Table

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

RighettiAndrea_0-1645627475317.png

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)
1 Solution

Accepted Solutions
RighettiAndrea
Contributor III
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!!

View solution in original post

5 Replies
Aurelien_Martinez
Partner - Specialist II
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!
RighettiAndrea
Contributor III
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]))

 

RighettiAndrea_0-1645686502614.png

 

MayilVahanan

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
Please close the thread by marking correct answer & give likes if you like the post.
RighettiAndrea
Contributor III
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.

 

RighettiAndrea_0-1645687962642.png

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

RighettiAndrea_1-1645688148859.png

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

 

thanks a lot

RighettiAndrea
Contributor III
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!!