Forum Discussion
Filtering Two Categories in a column
Hi folks, good day. Can you help me to filter two name categories in the Account Column: "Service Income—SC" and "Service Income—External" to show the value of the remaining categories in the Account Column? Can you show me how to filter those two using Dax? It's a big help if you answer my question. Thanks in advance!
| Month | Amount | Account |
| January | 86,234 | Service Income - SC |
| January | 98,234 | Service Income - SC |
| January | 4,952 | Service Income - SC |
| January | 57,586 | Service Income - SC |
| February | 32,293 | Service Income - External |
| February | 10,293 | Service Income - External |
| February | 9,836 | Service Income - External |
| February | 8,349 | Service Income - External |
| February | 15,294 | Service Income - External |
| March | 400.00 | PHIC Contributions-COS |
| March | 347.00 | PHIC Contributions-COS |
| March | 400.00 | PHIC Contributions-COS |
| March | 347.00 | PHIC Contributions-COS |
| March | 400.00 | PHIC Contributions-COS |
| March | 347.00 | PHIC Contributions-COS |
| March | 347.00 | PHIC Contributions-COS |
| April | 3,230 | Allowances - Medical-COS |
| April | 38,239 | Allowances - Medical-COS |
| April | 1,834 | Allowances - Medical-COS |
| April | 6,439 | Allowances - Medical-COS |
| April | 2,495 | Allowances - Medical-COS |
Hi JohnCarl45 ,
Hi, thanks for the information you have given.
To show the value of the remaining categories in the Account Column, you can use the DAX as follow:
Amount_ = var _t=CALCULATETABLE('Table', NOT('Table'[Account] IN {"Service Income - SC", "Service Income - External"})) RETURN MAXX(_t,[Amount])An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Joseph JiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Here is the formula I use to filter the categories, except for those two categories in the Account column. the "Service Income—SC" and "Service Income—External".
Cost of Service 3 = SUMX(FILTER(ALL(Sheet1,Sheet1[Account]), EXCEPT("Service Income - SC" || "Service Income - External"), 'Sheet1'[sum of amount])))JohnCarl45 Please try to create measure like this
Total of Account =CALCULATE(SUM('Table 1'[Amount]),FILTER('Table 1','Table 1'[Account] IN {"PHIC Contributions-COS","Allowances - Medical-COS"}))Pull the Account column and this measure you will see the output as belowHope this helps
Hi sir, good day. Thanks for the reply. I really appreciate it. Can you filter all the categories in the account column except for the two names in the account column, which are "PHIC Contributions-COS" and "Allowances-Medical-COS"? I really want to show all categories, except for two, because in the original data, there were almost 100k rows in Excel, so I want to show all the categories in the account, except for two.
Hi JohnCarl45 ,
Hi, thanks for the information you have given.
To show the value of the remaining categories in the Account Column, you can use the DAX as follow:
Amount_ = var _t=CALCULATETABLE('Table', NOT('Table'[Account] IN {"Service Income - SC", "Service Income - External"})) RETURN MAXX(_t,[Amount])An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Joseph JiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
