DAX - change denominator based on a filtered column value

[dkalina97]

Currently I am displaying a ratio that shows the expense $ by department divided by the total revenue.

(MTD Exp to Revenue % =

Divide([MTD Expense],('Actual Revenue'[Actual MTD Gross Revenue for Ratio])).

The numerator is defined by the department based on the chart of accounts, and the denomintor is the same for all departments in the column. MTD Expense is simply 

MTD Expense =

CALCULATE(Sum(AccountTransactions[Monthly Expense]),DATESMTD('Dates'[Date]))

What I would like to do is change the denominator so that, if Department = Assembly, the the Denominator would be Equipment Sales, Rather than Total Sales. 

[AlexisOlson · Accepted solution]

A SWITCH should do the trick.

MTD Exp to Revenue % =
VAR Denom =
    SWITCH (
        SELECTEDVALUE ( Department[Department] ),
        "Assembly", [Equipment Sales],
        "Other Department", [Different Sales Figure],
        [Total Sales]
    )
RETURN
    DIVIDE ( [MTD Expense], Denom )

[dkalina97]

How would I set up a second Variable? One other department would use a different sales figure, the remainder would be total sales.

[AlexisOlson · Accepted solution]

A SWITCH should do the trick.

MTD Exp to Revenue % =
VAR Denom =
    SWITCH (
        SELECTEDVALUE ( Department[Department] ),
        "Assembly", [Equipment Sales],
        "Other Department", [Different Sales Figure],
        [Total Sales]
    )
RETURN
    DIVIDE ( [MTD Expense], Denom )

[dkalina97]

This worked, thank you so much! My last question. Because of the weighting, how do I sum the measure? Example, $ Variance to Budget should be -21,033. It is doing the math of the percent minues the percent, times the grand total sales, just as the rows above, rather than summing the rows above. Thank you!

[AlexisOlson · Accepted solution]

You'll need to sum over the granularity you are showing in your visual (or a finer granularity).

SUMX ( VALUES ( Department[Department] ), [$ Variance to Budget B/(W)] )

[AlexisOlson]

You can set up the logic like this:

MTD Exp to Revenue % =
VAR Denom =
    IF (
        SELECTEDVALUE ( Department[Department] ) = "Assembly",
        [Equipment Sales],
        [Total Sales]
    )
RETURN
    DIVIDE ( [MTD Expense], Denom )