Calculate sales where category not = A


I have a table of sales and want to work with the data excluding one category of data (eg: category A).

I want to calculate sum of sales for all categories except category A, without having to list each category that is included.

Then work out sales share for all categories but also excluded category A from everything.

For example:

Any assistance would be appreciated.

P.s. i think I am over thinking this and should just do am if statement with a blank and a sum.

Eg filteredsales = IF ( data.Category = ‘A’, BLANK(), Sum[Sales] )

help pbi file.pbix (21.7 KB)

Hi jgriffit,

You could do something like this:

Sales ex A = CALCULATE(SUM(Sales[Sales]);Sales[Category]<>“A”)

Gr Daniels

Excellent, thanks. I got there in my mind in the car, came back and implemented. Then checked the board and here is the same result! Thanks for helping / confirming. If it wasn’t for the car ride and stepping away from the laptop, I may not have got it and REALLY needed your help. Thanks again for the reply.

Is it just me, or even when I do something that works well, a little par to me says, there is probably an easier or more efficient way … :slight_smile:

You’re welcome!

You could also use this formula to get your desired result. I would suggest watching Sam’s content on CALCULATE.

Filtered Sales =
CALCULATE ( SUM ( Sales[Sales] ), FILTER ( Sales, Sales[Category] <> “A” ) )