Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
User | Count |
---|---|
86 | |
82 | |
68 | |
67 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |