The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi I was able to create these 3 measures:
Total = (CALCULATE('Sales'[x], 'Customers'[Markets]="All of US"))
CustomSales = (CALCULATE(SWITCH(
SELECTEDVALUE('Customers'[Total US Markets]),
"Retailer 4",
CALCULATE([x],'ITEM Control'[Control Item] = "N"),
CALCULATE([$]))))
Division Equation =
SUMX (
VALUES ( 'Customers'[Markets]),
DIVIDE (
CustomSales,
CALCULATE ( Total, All('Customers'[Markets] )
) * 100
))
But I want to improve my DAX and just create one measure to have everything involved.
What the 'Total' Measure is doing is creating my dominator.
I have a bunch of Customers and one of them has this special use case where I need to use a switch statement to get the information for that retailer, this is essentially my numerator (there's 30 different retailers, the switch is only needed for retailer 4)
CustomSales = (CALCULATE(SWITCH(
SELECTEDVALUE('Customers'[Total US Markets]),
"Retailer 4",
CALCULATE([x],'ITEM Control'[Control Item] = "N"),
CALCULATE([$]))))
Now I have this measure doing this Retailer/Total * 100, for each specific retailer
Division Equation =
SUMX (
VALUES ( 'Customers'[Markets]),
DIVIDE (
CustomSales,
CALCULATE ( Total, All('Customers'[Markets] )
) * 100
))
Hoping to get all 3 into just one DAX measure I can copy and paste and send to friends.
DAX is often easier to understand if you break it into multiple logical components like you have. But if you need to put it all in a single measure, then you should be able to substitute in the Total and CustomSales into Division Equation.
Division Equation =
SUMX (
VALUES ( 'Customers'[Markets] ),
DIVIDE (
CALCULATE (
SWITCH (
SELECTEDVALUE ( 'Customers'[Total US Markets] ),
"Retailer 4", CALCULATE ( [x], 'ITEM Control'[Control Item] = "N" ),
CALCULATE ( [$] )
)
),
CALCULATE ( [x], 'Customers'[Markets] = "All of US" )
) * 100
)
I've done a bit of cleanup in the above. Straight substitution would look like this:
Division Equation =
SUMX (
VALUES ( 'Customers'[Markets] ),
DIVIDE (
(
CALCULATE (
SWITCH (
SELECTEDVALUE ( 'Customers'[Total US Markets] ),
"Retailer 4", CALCULATE ( [x], 'ITEM Control'[Control Item] = "N" ),
CALCULATE ( [$] )
)
)
),
CALCULATE (
( CALCULATE ( 'Sales'[x], 'Customers'[Markets] = "All of US" ) ),
ALL ( 'Customers'[Markets] )
) * 100
)
)
Hi I meant to put x for $, at the very end, of the switch statement
Master Dax Formula =
Total = (CALCULATE('Sales'[x], 'Customers'[Markets]="All of US"))
CustomSales = (CALCULATE(SWITCH(
SELECTEDVALUE('Customers'[Total US Markets]),
"Retailer 4",
CALCULATE([x],'ITEM Control'[Control Item] = "N"),
CALCULATE([x]))))
Division Equation =
SUMX (
VALUES ( 'Customers'[Markets]),
DIVIDE (
CustomSales,
CALCULATE ( Total, All('Customers'[Markets] )
) * 100
))
is actually my code im sorry.
Hi @Anonymous ,
What's your expected result? Could you please provide some raw data and explain the calculation logic using some specific examples and backend scenario? It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
33 | |
20 | |
16 | |
15 |
User | Count |
---|---|
56 | |
50 | |
36 | |
35 | |
31 |