[SOLVED] DAX column totals are not giving expected totals when using the MIN

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