Hi, I have set up a few DAX queries based around the amount of customers accounting for 80% of our sales when looking at products (although the formula will work on any related segmentation) -
var t1 = SUMMARIZE(
'Customer Transactions - Invoices',
Customers[Customer Name],
"Total Sales", [Total Invoiced Value (Net)]
)
var t2 = ADDCOLUMNS(t1,
"Rank", RANKX(t1, [Total Sales],,DESC,Dense)
)
var t3 = ADDCOLUMNS(t2,
"Cumulative Sales", SUMX(FILTER(t2, [Rank] <= EARLIER([Rank])), [Total Sales])
)
var total = MAXX(t3, [Cumulative Sales])
var t4 = ADDCOLUMNS(t3,
"Cumulative %", DIVIDE([Cumulative Sales], total)
)
var dividing_rank = MINX(FILTER(t4, [Cumulative %] >= 0.80), [Rank])
return SUMX(FILTER(t4, [Rank] <= dividing_rank),1)
To Summarize -
(The [Total Invoiced Value (Net)] measure is in the Invoices table, hence using it for the SUMMARIZE)
t1 = Customers with total sales
t2 = add Rank by total sales
t3 = add cumulative Total Sales by Rank
total = sum of total sales across t3
t4 = add cumulative % of total
dividing_rank = finds rank of customer that straddles over 80% boundary of Cumulative %
return = Count of rows up to and including all the dividing_rank customer
Now this works fine, if I add it to a table with products on rows it correctly counts the number of customers accounting for 80% of sales, even with multiple product hierarchy levels.
I've spent a few hours looking at alternative techniques (i.e. SUMMARIZECOLUMNS) as this just feels a bit overly complex for what I suspect is a reaosnably common pattern. I'm also wondering whether there is any way of splitting this into multiple measures as I have a few based around this same measure excluding the return (i.e. the amount of customers accounting for 20% of sales).
It may be that this is about right and it does just need duplicating for similar measures.
