Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
I'm trying to come up with a pivot (matrix) with [SKU] or rows that displays the number of Total Orders involving that SKU, and the number of single-SKU Orders. My data consists of [Date][OrderID][SKU] and [Qty] fields.
My DAX formula of:
nSingle-SKU Orders := COUNTROWS(FILTER(SUMMARIZE(Table,Table[OrderID],"nLines",COUNTA([SKU]),"TotalQty",SUM([Qty])),[nLines]=1))
only returns the number of total orders involving the SKU on that row, instead of those where it is the ONLY SKU for the order.
The row filter of the SKU doesn't allow the formula to see/count all the rows of the orders.
Suggestions please.
Solved! Go to Solution.
That's it!!! This is GREAT! Exactly what I was looking for... and it's fast
Thanks so much for taking the time to help me out.
Hi @TrebligC,
This is the simplest measure I can think of (I've named the table Orders)
Number of Single-SKU Orders = COUNTROWS ( FILTER ( VALUES ( Orders[OrderID] ), CALCULATE ( DISTINCTCOUNT ( Orders[SKU] ), ALL ( Orders[SKU] ) ) = 1 ) )
Output looks like:
That's it!!! This is GREAT! Exactly what I was looking for... and it's fast
Thanks so much for taking the time to help me out.
MEASURE AND REPORT
MEASURE N TABULAR REPORT
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
75 | |
56 | |
38 | |
34 |
User | Count |
---|---|
99 | |
56 | |
51 | |
44 | |
40 |