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:
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.