Help with DAX for SumX and Filter function on multiple columns

[powerbihelp87]

I need to calculate the sum of two columns based on filters. I tried:

SUMX(FILTER('Test Table','Test Table'[Code="10023" || 'Test Table'[Code]="10039" && 'Test Table'[Category]="az" && 'Test Table'[Category]="ac" ),'Test Table'[ Amount]) it did not work.

Can someone please help guide me how I can get the sum for codes 10039 or 10023 where categories are ac or az. the actual table has more columns like date, brand which i need to be able to filter on dynamically

Category	Code	Amount
ab	10023	192
ac	10039	3092
kj	10023	49
ko	1093	242
ab	1098	402
ac	10023	4
az	10039	2543
ab	1890	432
ac	10023	9323
kj	10304	3894
ko	1930	493
ab	2931	292
ac	2930	923
az	10023	587
ab	10039	8437
ac	2930	8329
kj	10023	94389
ko	10039	289
ab	2304	94
ac	2904	7
az	10023	21

[AntrikshSharma · Accepted solution]

Try this:

=
SUMX (
    FILTER (
        ALLSELECTED ( 'Test Table' ),
        ( 'Test Table'[Code] = "10023"
            || 'Test Table'[Code] = "10039" )
            && ( 'Test Table'[Category] = "az"
            || 'Test Table'[Category] = "ac" )
    ),
    'Test Table'[ Amount]
)

[powerbihelp87]

@AntrikshSharma  actually it works at total level and when I want to filter.  But if I add a filter like plant into a table it shows total and won't break it down unless i filter.   but once i filter on lets say C01 the numbers appear?

once i filter numbers break down properly.  I think i solved this by removing the ALLSELECTED

[AntrikshSharma]

Cool, I thought you wanted total for the conditions plus anything that is selected through slicers.

[powerbihelp87]

@AntrikshSharma  Yes, you are right initially I did but then realized I need to see more data.  thanks!

[powerbihelp87]

Amazing! Thank you so much I was mixing up the && || and didn't include ALL!

thank you!!!!!

[parry2k]

@powerbihelp87 try this measure

SUM ( 'Test Table'[Amount]),
'Test Table','Test Table'[Code] IN {"10023","10039"},
'Test Table'[Category] IN {"az""ac"} 
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡