Forum Discussion
DAX Switch statement not providing totals value
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)
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
2 Replies
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
I appreciate it. This was the correct solution I was looking for!
