Filtering Two Categories in a column

[JohnCarl45]

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

[v-jincheng-msft · Accepted solution]

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 Ji

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Chakravarthy]

@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 below

Hope this helps

[JohnCarl45]

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.

[JohnCarl45]

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])))