Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you, Pat. That looks to be working well - I didn't even think of trying a distinct count in that way.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |