DAX Switch statement not providing totals value

[DCrone]

I have a dax switch statement that produces the correct numbers inside the table, but I am not able to get it to sum the column into a grand total. I attempted using a sumx statement, however it doesn't seem to produce a different result. Oddly, if I separate it into 2 measures, then it does show the total. What is the best practice to sum this column and keep its respective filters?

Switch Value = 
VAR CALC1 = 
SWITCH(
    TRUE(),
    MAX(dimensionValues[Department Number]) IN {"029", "032"}, CALCULATE(SUM(generalLedgerEntries[amount]) * -1, generalLedgerAccounts[accountNumber] = "4700"),
    MAX(dimensionValues[Department Number]) IN {"030", "031", "036"}, CALCULATE(SUM(generalLedgerEntries[amount]) * -1, generalLedgerAccounts[accountNumber] IN {"4700","4701"}),
    MAX(dimensionValues[Department Number]) IN {"038"}, CALCULATE(SUM(generalLedgerEntries[amount]) * -1, generalLedgerAccounts[accountNumber] IN {"4700"}),
    MAX(dimensionValues[Department Number]) IN {"039"}, CALCULATE(SUM(generalLedgerEntries[amount]) * -1, generalLedgerAccounts[accountNumber] IN {"4700"}),
    MAX(dimensionValues[Department Number]) IN {"000", "033"}, CALCULATE(SUM(generalLedgerEntries[amount]) * -1, generalLedgerAccounts[accountNumber] >= "4604" && generalLedgerAccounts[accountNumber] <= "4610" || generalLedgerAccounts[accountNumber] IN {"4615"}),
    BLANK()
)

VAR CALC2 =
SUMX(
    VALUES(dimensionValues[Department Name]), CALC1
)

RETURN CALC2

Result it produces (Blank Total)

[Anonymous · Accepted solution]

Hi @DCrone ,

Please try this. If below DAX doesn't work, please provide sample data or pbix file(exclude sensitive data).

Switch Value = 
SUMX(
    VALUES(dimensionValues[Department Name]), CALCULATE(SWITCH(
    TRUE(),
    MAX(dimensionValues[Department Number]) IN {"029", "032"}, CALCULATE(SUM(generalLedgerEntries[amount]) * -1, generalLedgerAccounts[accountNumber] = "4700"),
    MAX(dimensionValues[Department Number]) IN {"030", "031", "036"}, CALCULATE(SUM(generalLedgerEntries[amount]) * -1, generalLedgerAccounts[accountNumber] IN {"4700","4701"}),
    MAX(dimensionValues[Department Number]) IN {"038"}, CALCULATE(SUM(generalLedgerEntries[amount]) * -1, generalLedgerAccounts[accountNumber] IN {"4700"}),
    MAX(dimensionValues[Department Number]) IN {"039"}, CALCULATE(SUM(generalLedgerEntries[amount]) * -1, generalLedgerAccounts[accountNumber] IN {"4700"}),
    MAX(dimensionValues[Department Number]) IN {"000", "033"}, CALCULATE(SUM(generalLedgerEntries[amount]) * -1, generalLedgerAccounts[accountNumber] >= "4604" && generalLedgerAccounts[accountNumber] <= "4610" || generalLedgerAccounts[accountNumber] IN {"4615"}),
    BLANK()
))
)

——————————————————————————————————————————————————

If my answer helps you solve the problem, please accept my answer as a solution and let it be seen by more people in need.

Best regards,

Mengmeng Li

[DCrone]

I appreciate it. This was the correct solution I was looking for!