
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Percentage % of Units by dimension (month)
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
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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],

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanx! will try that. BR Peter

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Exactly what I did! and now it is ok 🙂
Thanx for everything
