Trying to mix my two variables with my Sum X function, need help!

[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.

[Anonymous · Accepted 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.