Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Complete newbie here working my way through an online course and starting out.
I have some sales data and I'd like to produce a report that shows how many items of a certain product appear in orders.
For instance here is a filtered table showing product 'Widgy Biscuit'
Order ID | Product Qty | Product Name |
1336327 | 4 | Widgy Biscuit |
1336333 | 1 | Widgy Biscuit |
1336340 | 2 | Widgy Biscuit |
1336340 | 2 | Widgy Biscuit |
1336340 | 2 | Widgy Biscuit |
1336340 | 1 | Widgy Biscuit |
1336340 | 1 | Widgy Biscuit |
1336340 | 2 | Widgy Biscuit |
1336347 | 1 | Widgy Biscuit |
1336347 | 1 | Widgy Biscuit |
1336347 | 1 | Widgy Biscuit |
1336347 | 1 | Widgy Biscuit |
1336347 | 1 | Widgy Biscuit |
1336354 | 5 | Widgy Biscuit |
1336356 | 1 | Widgy Biscuit |
1336371 | 1 | Widgy Biscuit |
1336371 | 1 | Widgy Biscuit |
You can see the order numbers in column 'Order ID'
An example is order 1336340.
It has 6 lines of 'Widgy Biscuit', and some lines have Product QTY of > 1.
The result of order 1336340 would be '10' as that is the sum of the Product QTY column for all instances of 'Widgy Biscuit' within that Order ID.
In terms of results for the table above, I'd like an output similar to:
How many Widgy Biscuit in order | Number of Orders |
1 | 2 |
2 | 0 |
3 | 1 |
4 | 1 |
5 | 2 |
6 | 0 |
7 | 0 |
8 | 0 |
9 | 0 |
10 | 1 |
Reasoning:
There are 2 orders with 1 QTY of 'Widgy Biscuit' (1336333 and 1336356)
There is 1 order with 3 QTY of 'Widgy Biscuit' (1336371)
There is 1 order with 4 QTY of 'Widgy Biscuit' (1336327)
There are 2 orders with 5 QTY of 'Widgy Biscuit' (1336347 and 1336354)
There is 1 order with 10 QTY of 'Widgy Biscuit' (1336340)
There are no matches for the other quantities in this table.
What is the best and most efficient way to achieve this?
Solved! Go to Solution.
Hi @JimmyAck ,
First create a calculated column as below:
Qty = SUMX(FILTER('Table','Table'[Order ID]=EARLIER('Table'[Order ID])&&'Table'[Product Name]=EARLIER('Table'[Product Name])),'Table'[Product Qty])
Then create a measure as below:
How many Widgy Biscuit in order Number of Orders = CALCULATE(DISTINCTCOUNT('Table'[Order ID]),ALLEXCEPT('Table','Table'[Qty]))
And you will see:
There is 1 order with 3 QTY of 'Widgy Biscuit' (1336371),I have checked order 1336371,the total qty should be 2,not 3.
For the related .pbix file,pls click here.
Hi @JimmyAck ,
First create a calculated column as below:
Qty = SUMX(FILTER('Table','Table'[Order ID]=EARLIER('Table'[Order ID])&&'Table'[Product Name]=EARLIER('Table'[Product Name])),'Table'[Product Qty])
Then create a measure as below:
How many Widgy Biscuit in order Number of Orders = CALCULATE(DISTINCTCOUNT('Table'[Order ID]),ALLEXCEPT('Table','Table'[Qty]))
And you will see:
There is 1 order with 3 QTY of 'Widgy Biscuit' (1336371),I have checked order 1336371,the total qty should be 2,not 3.
For the related .pbix file,pls click here.
hi @v-kelly-msft ,
Thank you for that. It works well but I probably should have added a little bit more information.
My real sales data source has around 300 SKUs, or in the context of my table above 'Product Name's.
When I introduce more than one product name I get the same result for all products - the formula is summing the entire quantity of every 'Product QTY' of a unique 'ORDER ID'.
My fault for not providing all of the data in the first place! Sorry.
Is there a way that I can do this so that when I filter on each unique 'Product Name' it provides the 'QTY' just for that item?
Or would I need a unique measure per product name?
Thanks for you help.
I haven't done error checking across the board yet but I added to the measure formula:
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |