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