Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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.
User | Count |
---|---|
112 | |
94 | |
87 | |
75 | |
65 |
User | Count |
---|---|
138 | |
112 | |
110 | |
98 | |
94 |