Sum Column with Multiple Criteria

[igreeinf]

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!

[v-piga-msft · Accepted solution]

Hi @igreeinf ,

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

[az38]

Hi @igreeinf 

try new measure

Measure = calculate(sum(Tbl[Sales]);ALLEXCEPT(Tbl;Tbl[Year];Tbl[Month];Tbl[Cat1]))

[igreeinf]

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?

[az38]

@igreeinf 

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?