// Say you have:
// 1. Dimension Customer
// 2. Fact Sales
// and they're connected this way:
// Customer 1:* Sales
// This is somewhat standard.
// Apart from this there are maybe
// other dimensions connected to
// the fact table.
// You need to define the following:
[Sales] = sum(Sales[Sales])
// Idea: Go through all selected/visible customers
// and find their cumulative share relative
// to the all the selected/visible customers.
// Once you have the table, find the number of
// them with cumulative share <= 80%.
// Attention: This measure
// is to be used on its own. If you put
// it in a table that displays Customers
// one by one, you'll get a correct result
// but NOT what you expect. It means it'll
// calculate something different than what
// you think it should calculate.
// Assumption: Customer[Customer Name] is
// unique. If not, then [Customer Name]
// should be replaced with a unique identifier
// in the Customer dimension.
[#Cust Where Cumul Sales <= 80%] =
var __threshold = .8
var __totalSales = [Sales]
var __custsWithSales =
addcolumns(
values(Customer[Customer Name]),
"CustSales", [Sales]
)
var __custsCount =
countrows(
filter(
__custsWithSales,
var __currentSales = [CustSales]
var __cummulativeSales =
sumx(
__custsWithSales,
[CustSales] * ([CustSales] >= __currentSales)
)
return
divide(__cummulativeSales, __totalSales) <= __threshold
)
)
return
__custsCount
Please bear in mind that I've written this without actually running it because I don't have time to create a model and populate with data. You might need to adjust this but I think it should be OK. Let me know if it's not.
Best
D
