Percentage % of Units by dimension (month)

[tigra999]

Hello,

I want to show in a graph the % of total order intake by month (for any selected dimension - in this case ord type) in a graph that show several years by month. 

I currently use this expression: 

Sum({<item_type ={1}>} quantity_ordered)/Sum(TOTAL {<item_type ={1}>} quantity_ordered)

but obviously it show the % based on the Total number of units (all months) instead of the isolated month. I would like to see  a total of 100% for each month.

Expression for "Ord Yr/Month" as per below (if it helps you to solve this matter...)
=YEAR (order_date) & Month (order_date)

Is there an easy formula to get this to work?

Current graph below:

BR & thanx in advance Peter

[lorenzoconforti]

You need to use your "Ord Yr/Month" dimension within the total qualifier:

Sum({<item_type ={1}>} quantity_ordered)/Sum(TOTAL <[Ord Yr/Month]> {<item_type ={1}>} quantity_ordered)

The problem is that it is a calculated dimension and it won't work. You just need to create it in you data during load; something like:

YEAR (order_date) & Month (order_date) as [Ord Yr/Month],

[tigra999]

Thanx! will try that.  BR Peter

[tigra999]

Hi again, partly solved 🙂

But I cant get the OrdYrMonth dimension showing the year+month, now it is 0-12 where the 0 is probably the total of full year (pic1), is the only way to use an expression to solve this?

If I select one individual year such as 2020 (selected on original "order_date") I get only the total (pic2) - should 3 months
If I select one individual year such as 2019 (selected on original "order_date" I get 3 months + total .. (pic3) -

Load script used now:
YEAR (order_date) & Month (order_date) as "OrdYrMonth",

Expression used:
Sum({<item_type ={1}>} quantity_ordered)/Sum(TOTAL <[OrdYrMonth]> {<item_type ={1}>} quantity_ordered)

Any ideas whats gone wrong?

BR Peter

pic1:

pic2:

pic3:

[lorenzoconforti]

Strange

Can you just create a table with 4 dimensions to see what's going on?

OrdYrMonth

order_date

YEAR (order_date)

Month (order_date)

[tigra999 · Accepted solution]

Exactly what I did! and now it is ok 🙂

Thanx for everything