powerbiswitch-statementdaxmeasure

DAX column totals are not giving expected totals when using the MIN


Name Category Item Amount
Gary food rice 1
Gary food sugar 5
Gary furniture chair 8
Gary furniture table 4
John food rice 3
John food sugar 5
John furniture chair 6
John furniture table 7
Name Food Furniture Total
Gary 12 36 48
John 16 39 55

Hello All Trying to write this Dax but the column totals in power bi are giving me wrong totals. It gives the totals as 36 and 42 which is not the case.

The Dax is supposed to group the categories by name of person and multiply the total food category by 2 and multiply total furniture category by 3.

This is my Dax:

SWITCH(
    TRUE(),
    MIN(Sheet1[Category]) = "Food", SUM(Sheet1[Amount]) * 2,
    MIN(Sheet1[Category]) = "Furniture", SUM(Sheet1[Amount]) * 3
)

Solution

  • Well, I would say that the results are perfectly in line with your measure. The issue with your DAX formula is that it does not handle the totals correctly. The SWITCH statement with SUM operates row by row but doesn't adjust the totals at the category level. For proper totals, you can try using a measure that groups and aggregates at the category level first, then applies the multiplier.

    Try the following approach:

    Total Amount = 
    SUMX(
        VALUES(Sheet1[Category]),
        SWITCH(
            TRUE(),
            Sheet1[Category] = "Food", CALCULATE(SUM(Sheet1[Amount]) * 2),
            Sheet1[Category] = "Furniture", CALCULATE(SUM(Sheet1[Amount]) * 3),
            0
        )
    )