Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone and I hope you can help me with this problem.
In my data, An order contains of one or many rows of products and I want to analyze orders based on the total size of the products.
The problem comes when I want to have country as a dimension and calculate the % of each "aggregated group" per country, and to show this all in a a pivot table.
Dimension row: =[Country]
Dimension Column: =aggr(sum([#SALES_QTY]), [Order no])
(To sum all products for each order so I truly know how many products are within the orders)
Measure (right now but I want more): count(distinct [Order no])
This how the table looks now
What I want to happen is that each number is divided by the total number of orders for that country so I can see, as for Finland, that 6.6% of orders has a QTY of 1. aka 20 / (20 + 42 + 48 + 92 + 101).
The problem I'm facing is that I'm either limited to values within the aggregated group or to the whole dataset, but I want the sum of the row.
I appreciate any suggestions or hints of where I can find a solution, I would prefer to solve this within chart expressions (due to some limitations accessing the load script) but I'm starting to think that my problem should be solved there..
best regards David
I'm not sure I understand the requirement correctly, but it sounds like what you're after is
count(distinct [Order no])
/
Count(Total <Country> distinct [Order no])
I'm not sure I understand the requirement correctly, but it sounds like what you're after is
count(distinct [Order no])
/
Count(Total <Country> distinct [Order no])
Thanks, this is exactly what I needed. It's frustrating when my own incompetence is the real issue.. Thanks again and take care.