Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JimmyAck
New Member

Sales data - Counting how much productX in order over multiple lines

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 IDProduct QtyProduct Name
13363274Widgy Biscuit
13363331Widgy Biscuit
13363402Widgy Biscuit
13363402Widgy Biscuit
13363402Widgy Biscuit
13363401Widgy Biscuit
13363401Widgy Biscuit
13363402Widgy Biscuit
13363471Widgy Biscuit
13363471Widgy Biscuit
13363471Widgy Biscuit
13363471Widgy Biscuit
13363471Widgy Biscuit
13363545Widgy Biscuit
13363561Widgy Biscuit
13363711Widgy Biscuit
13363711Widgy 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 orderNumber of Orders
12
20
31
41
52
60
70
80
90
101

 

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?

 

 

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

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:

Annotation 2020-06-04 121047.png

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.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

3 REPLIES 3
v-kelly-msft
Community Support
Community Support

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:

Annotation 2020-06-04 121047.png

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.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

 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:

= CALCULATE(DISTINCTCOUNT('Table'[Order ID]),ALLEXCEPT('Table','Table'[Qty],'Table'[Product Name]))
and I think that has done the trick.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.