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.
Solved! Go to Solution.
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:
This is a must watch for a message from Power BI!
Click here to read more about the December 2020 Updates!
Click here to read the latest blog and learn more about contributing to the Power BI blog!
Mark your calendars and join us for our next Power BI Dev Camp!.