Forum Discussion
Sum Column with Multiple Criteria
Hi All,
Trying to acheive something like this in "New Column", using a DAX function.
I want to sum the Sales values for matching Year, Month, and Cat1 rows.
| Year | Month | Cat1 | Cat2 | Sales | New Column |
| 2019 | Feb | Quick | Fizz | 1 | 3 |
| 2019 | Feb | Quick | Buzz | 2 | 3 |
| 2019 | Feb | Brown | Fizz | 3 | 7 |
| 2019 | Feb | Brown | Buzz | 4 | 7 |
| 2019 | March | Quick | Fizz | 5 | 11 |
| 2019 | March | Quick | Buzz | 6 | 11 |
| 2019 | March | Brown | Fizz | 7 | 15 |
| 2019 | March | Brown | Buzz | 8 | 15 |
Thanks!
Hi Anonymous ,
You also could try this calculated column.
Column = CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year] = EARLIER ( 'Table'[Year] ) && 'Table'[Month] = EARLIER ( 'Table'[Month] ) && 'Table'[Cat1] = EARLIER ( 'Table'[Cat1] ) ) )Here is the output.
Hope this can help you.
Best Regards,
Cherry
4 Replies
Hi Anonymous
try new measure
Measure = calculate(sum(Tbl[Sales]);ALLEXCEPT(Tbl;Tbl[Year];Tbl[Month];Tbl[Cat1]))
Thanks az38! I tried that and it worked before, but it's giving me values for Sales that don't exist at all, which is strange.
Any insight?
Anonymous
i think, this is the easiest case of dax-usage
what kind of incorrect values it's giving you? could you show an example to try to debug? maybe some filters?
Hi Anonymous ,
You also could try this calculated column.
Column = CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year] = EARLIER ( 'Table'[Year] ) && 'Table'[Month] = EARLIER ( 'Table'[Month] ) && 'Table'[Cat1] = EARLIER ( 'Table'[Cat1] ) ) )Here is the output.
Hope this can help you.
Best Regards,
Cherry
