Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!!
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
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]))
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)
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
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!!