Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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([x]))))
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.
Solved! Go to Solution.
Hi @Anonymous ,
I think you can try VAR() and RETURN to achieve your goal. The new measure looks like as below.
Division Equation =
VAR Total =
( CALCULATE ( 'Sales'[x], 'Customers'[Markets] = "All of US" ) )
VAR CustomSales =
(
CALCULATE (
SWITCH (
SELECTEDVALUE ( 'Customers'[Total US Markets] ),
"Retailer 4", CALCULATE ( [x], 'ITEM Control'[Control Item] = "N" ),
CALCULATE ( [x] )
)
)
)
RETURN
SUMX (
VALUES ( 'Customers'[Markets] ),
DIVIDE ( CustomSales, CALCULATE ( Total, ALL ( 'Customers'[Markets] ) ) * 100 )
)
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
I think you can try VAR() and RETURN to achieve your goal. The new measure looks like as below.
Division Equation =
VAR Total =
( CALCULATE ( 'Sales'[x], 'Customers'[Markets] = "All of US" ) )
VAR CustomSales =
(
CALCULATE (
SWITCH (
SELECTEDVALUE ( 'Customers'[Total US Markets] ),
"Retailer 4", CALCULATE ( [x], 'ITEM Control'[Control Item] = "N" ),
CALCULATE ( [x] )
)
)
)
RETURN
SUMX (
VALUES ( 'Customers'[Markets] ),
DIVIDE ( CustomSales, CALCULATE ( Total, ALL ( 'Customers'[Markets] ) ) * 100 )
)
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
12 | |
10 | |
10 | |
6 |