Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi DAX expert, Can you help with the DAX : How many companies contributed to the Top80% of Sales..
// 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
Hi @Anonymous , because I always break down my measures into pieces to make it easier to write, read and understand, I'd write my measures like below, assuming your final result should be a number.
If this answers your question, kindly kudo and mark as solution to enable many more people gain from it.
how do i write the DAX if i do not have a Cumulative calculation column?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
20 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |