Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi! I have a pivottable where I first have a dimension "Month of order" and then Country.
I want to show share of sales in next column for every different row. I want the share of sales to summarize as below. How do I write that aggr-expression?
Looks like ;
Month of order Net sales Share of sales
January
Total 5M 50 % , (50 % of the year-total is in january)
USA 2 M 40 % , (40 % of january total is in US)
Canada 1 M 20 %
Mexico 1 M 20 %
Other 1M... 20 %
February
Total 5M 50 %
USA 1 20 %
Canada 1 20 %
Mexico 1 20 %
Other 2 40 %
Mar...
sum(netsales)/sum(total <Month>,<Country> netsales)
try this
Hi. I tried that one but it didn´t work. Maybe you can find out what´s wrong in the syntax?
May be this
If(Dimensionality() = 2,
Sum([Net Sales Amount])/Sum(TOTAL <[Month of Order]> [Net Sales Amount]),
Sum([Net Sales Amount])/Sum(TOTAL [Net Sales Amount])
)
Thanks! However I realized there is one more thing I need to take into account.
It shall only count the net sales amount for current year. I tried to put in my expression for current year. This one: Sum({$<$(vL.CY)>}[Net Sales Amount])
Syntax says ok when i Put it in the first part of expression, like this:
If(Dimensionality() = 2,
Sum({$<$(vL.CY)>}[Net Sales Amount])/Sum(TOTAL <[Booking Month]> [Net Sales Amount]),
Sum({$<$(vL.CY)>}[Net Sales Amount])/Sum(TOTAL <CountryCustomer> [Net Sales Amount])
)
However, I need the rows to have the last part also sum total for just current year, I tried this but without success:
Sum({$<$(vL.CY)>}[Net Sales Amount])/Sum(TOTAL <CountryCustomer> ({$<$(vL.CY)>}[Net Sales Amount]))
Can you check if this works?
Sum({$<$(vL.CY)>}[Net Sales Amount])
/
Sum(TOTAL <CountryCustomer> {$<$(vL.CY)>}[Net Sales Amount])
It says ok but gives just null-values. At the bottom of the picture you also see how the vl.CY-variable look. It worked to change to current year like that for the numerator-part of the expression but not for the denominator.. Then it just gives null-values.. Any idea?
Not sure I see any issue? Would you be able to share a sample to show the issue?