Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.