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
00kennedyp
Frequent Visitor

Distinct Count Order that contain list of SKUs

Sorry this has likely be answered but could find anything close to what Im looking for. 

 

I want to do a distinct count of orders that contain a list of SKUS. The orders can have other skus outside of the list but I only want to count the orders with all of the SKUs. 

 

For example 

 

Order 1 has  SKU A, B , C, D

Order 2 has  SKU A, B , C, E

Order 3 has  SKU A, B , E, G

Order 4 has  SKU C, D

 

 

I want to have a count of orders that contain the SKUs C & D so therefore Order 1 & 4 would be counted. 

 

This that possible

 

1 ACCEPTED SOLUTION

@00kennedyp

 

This is the most efficient measure I can think of (I've called your table Orders):

 

PBIX link

 

Number of Orders Containing All Selected SKUs = 
VAR SelectedSKUs =
    ALLSELECTED ( Orders[SKU] )
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( Orders[OrderNo] ),
            ISEMPTY ( EXCEPT ( SelectedSKUs, CALCULATETABLE ( VALUES ( Orders[SKU] ) ) ) )
        )
    )

 

This measure looks at the SKUs selected (by slicer say), 

By the way, SKUs 2957 & 5649 appear in 4 orders from the data you posted.

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

12 REPLIES 12
Ashish_Mathur
Super User
Super User

Hi,

 

How is the base data arranged?  Paste the data here.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Data would be something similar to this 

 

 

Order ID, SKU, Price. Net Etc...

 

000001, A, 7.99, 3.99

000001, B, 5.99, 2.99

000001, C, 8.99, 1.99

000001, D, 7.99, 3.99

000002, E, 7.99, 3.99

000002, C, 7.99, 3.99

000003, A, 7.99, 3.99

....

@00kennedyp,

Create a measure using the formula that Ashish provides in your table, then drag the measure to a card visual, and create a table visual using SKU field and the measure. Then use the SKU slicer to filter your visuals.

1.JPG2.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft

 

I'm not looking for distinct count of orders by SKUs.

 

Im trying to get a count of the order thats contain  a list of SKUs.

 

For example, I want a count of order which have SKU A & B within the order ID 

 

So the count would be 2 as both order 000001 & 000002 have the SKU A&B 

 

Does that make sense, I think I need to do an IF or CASE statement but Im not 100% sure

 

 

Order ID, SKU, Price. Net Etc...

 

000001, A, 7.99, 3.99

000001, B, 5.99, 2.99

000001, C, 8.99, 1.99

000001, D, 7.99, 3.99

000002, E, 7.99, 3.99

000002, C, 7.99, 3.99

000003, A, 7.99, 3.99

000003, B, 5.99, 2.99

Added some example data if this helps in the example data order number 881133, 881955 & 893460 have the SKUs 2957 & 5649. So I need the calculation to return a 3 as there are only these 3 orders which contain the filtered SKUs

Note I need the calculation to work with potentially upto 7 SKUs so AND() will not work.

 

OrderNoSKU
8811332957
8811335649
8817133524
88193792041
8819375649
8819552957
8819555649
88202139546
88202139522
8820215659
88202239522
88213239522
88317739522
8842502957
8843063524
8845862957
8845865649
8858186607
88781184310
8893236607
89075121378
89103616064
8914732957
89223057736
89277816064
89287316064
89287516064
89299777421
89325439542
89326216064
89331916064
8934602957
8934605649
89354077421
89384690777
89385366298

Hi,

 

The result should be 4 - 881133, 881955, 884586 and 893460

 

=CALCULATE(DISTINCTCOUNT(Data[OrderNo]),FILTER(SUMMARIZE(Data,Data[OrderNo],"ABCD",DISTINCTCOUNT(Data[SKU])),[ABCD]>=2))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@00kennedyp

 

This is the most efficient measure I can think of (I've called your table Orders):

 

PBIX link

 

Number of Orders Containing All Selected SKUs = 
VAR SelectedSKUs =
    ALLSELECTED ( Orders[SKU] )
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( Orders[OrderNo] ),
            ISEMPTY ( EXCEPT ( SelectedSKUs, CALCULATETABLE ( VALUES ( Orders[SKU] ) ) ) )
        )
    )

 

This measure looks at the SKUs selected (by slicer say), 

By the way, SKUs 2957 & 5649 appear in 4 orders from the data you posted.

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi @OwenAuger

 

Is there anyway to specify the selected SKU within the measurement?

 

I need to to several of this in scorecards in one report, so a slider would not work for me 

 

thanks 

 

Phil

Hi Phil

 

I would actually suggest you have a separate slicer for each set of SKUs you are interested in, and Format=>Edit Interactions so certain slicers control certain visuals.

 

I edited my example pbix to show an example of this.

 

If you did want to embed a SKU filter in a measure, you could write another measure referring to the original measure that looks like:

Orders containing 2957 and 5649 = 
CALCULATE (
	[Number of Orders Containing All Selected SKUs],
	Orders[SKU] IN {2957,5649}
)

 

Owen 🙂

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

@OwenAuger, Fixed it myself, thats for the help.

Phil, is this what you are looking for?

Number of Orders Containing All Selected SKUs = 
VAR SelectedSKUs =
    { 2957, 5649 }
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( Orders[OrderNo] ),
            ISEMPTY ( EXCEPT ( SelectedSKUs, CALCULATETABLE ( VALUES ( Orders[SKU] ) ) ) )
        )
    )

Hi,

 

Drag ID in the Table and then use =DISTINCTCOUNT(Table1[Order ID])

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.