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.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |