Filtering Customers based on Sales in Different Categories

[paulenomial]

Our organisation is new to Power BI and I'm trying to do some things that we were able to do in our previous BI software. I've been banging my head against the wall trying to do the below.

I'm trying to create a measure that shows the quantity sold of a certain category of item, but to only include customers who have never bought items from another, separate category. See some sample data below:

Sales number	Date	Customer	Category	Quantity
SO-00000001	01/01/2022	Customer 1	Category A	100
SO-00000002	01/01/2022	Customer 1	Category B	50
SO-00000003	01/02/2022	Customer 2	Category A	65
SO-00000004	01/03/2022	Customer 2	Category C	70

For example, say I wanted sales of items in category A for customers who have never bought anything in category B. The measure should disregard Customer 1 (as sales from Category B are non-zero for that customer) but include Customer 2 - though only show sales for Category A. The measure should therefore show a quantity of 65 in this case.

Thanks in advance for any help.

[Ashish_Mathur]

Hi,

Interesting one to solve.  Considering there will be many customers and categoies, hard coding the Category in the measure will not be a viable solution.  Could you share more rows of data in your sample please.  I'd like to try something.

Thank you. 

[mahoneypat · Accepted solution]

Here is a measure expression you can use. Replace Sales2 with your actual table name.

No Cat B Sales =
VAR NoBSales =
    FILTER (
        DISTINCT ( Sales2[Customer] ),
        ISBLANK (
            CALCULATE ( COUNT ( Sales2[Customer] ), Sales2[Category] = "Category B" )
        )
    )
RETURN
    SUMX (
        NoBSales,
        CALCULATE ( SUM ( Sales2[Quantity] ), Sales2[Category] = "Category A" )
    )

Pat

[paulenomial]

Thank you, Pat. That looks to be working well - I didn't even think of trying a distinct count in that way.