Need Help Consolidating my 3 Measures into 1!

[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([$]))))

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.

[AlexisOlson]

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

[Anonymous]

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. 

[v-yiruan-msft]

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