Share of sale, aggr over 2 different dimensions

[Bobi]

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

[Channa]

sum(netsales)/sum(total <Month>,<Country> netsales)

try this

[Bobi]

Hi. I tried that one but it didn´t work. Maybe you can find out what´s wrong in the syntax?

[sunny_talwar]

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

[Bobi]

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

[sunny_talwar]

Can you check if this works?

Sum({$<$(vL.CY)>}[Net Sales Amount])
/
Sum(TOTAL <CountryCustomer> {$<$(vL.CY)>}[Net Sales Amount])

[Bobi]

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?

[sunny_talwar]

Not sure I see any issue? Would you be able to share a sample to show the issue?