topic Re: Need Help Consolidating my 3 Measures into 1! in DAX Commands and Tips

Need Help Consolidating my 3 Measures into 1!

Anonymous — Fri, 04 Nov 2022 15:45:24 GMT

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.

Re: Need Help Consolidating my 3 Measures into 1!

AlexisOlson — Sat, 05 Nov 2022 22:11:07 GMT

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 ) )

Re: Need Help Consolidating my 3 Measures into 1!

Anonymous — Sun, 06 Nov 2022 03:58:27 GMT

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.

Re: Need Help Consolidating my 3 Measures into 1!

Anonymous — Tue, 29 Nov 2022 07:50:54 GMT

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