Forum Discussion
Help with DAX for SumX and Filter function on multiple columns
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 |
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] )
6 Replies
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] )Amazing! Thank you so much I was mixing up the && || and didn't include ALL!
thank you!!!!!
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
Cool, I thought you wanted total for the conditions plus anything that is selected through slicers.
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.⚡
