DAX Calculation to mesh my variables together

[Anonymous]

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.

[ValtteriN]

Hi,

I am not 100% I follow, but in essence instead of having 3 measures you want to combine the logic used in the measures?

The most straight-forward way to do this is to place the logic you now have in measures into variables.
E.g. here I am creating a calculated table with a column which gets a boolean value based on mindate calculation:

Vartable_example =

var _mindate = CALCULATE(MIN(VartableExample[Date]),ALL(VartableExample))

var _vartable =

ADDCOLUMNS(

    FILTER(ALL(VartableExample),VartableExample[Date] = _mindate ),"Test",IF(VartableExample[Date]>=TODAY(),1,0))

return

SUMX(_vartable,[Test])

I could do this by using measures but instead here I am placing the calculation logic in variables. You can utilize this logic by doign something like this:

Measure = 

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

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Anonymous]

Hey this didn't work, I tried and what happened was that it took the 'All of US' values and dividied that... so in my table it shows like that, i need it to be like the middle column 

[Anonymous]

Still need help 😕