cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Distinct Count Order that contain list of SKUs

@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!

Connect on Twitter
Connect on LinkedIn

View solution in original post

12 REPLIES 12
Highlighted
Super User IV
Super User IV

Re: Distinct Count Order that contain list of SKUs

Hi,

 

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


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

Re: Distinct Count Order that contain list of SKUs

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

....

Highlighted
Super User IV
Super User IV

Re: Distinct Count Order that contain list of SKUs

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/
Highlighted
Microsoft
Microsoft

Re: Distinct Count Order that contain list of SKUs

@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.
Highlighted
Frequent Visitor

Re: Distinct Count Order that contain list of SKUs

@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

Highlighted
Frequent Visitor

Re: Distinct Count Order that contain list of SKUs

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
Highlighted
Community Champion
Community Champion

Re: Distinct Count Order that contain list of SKUs

@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!

Connect on Twitter
Connect on LinkedIn

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Distinct Count Order that contain list of SKUs

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/
Highlighted
Frequent Visitor

Re: Distinct Count Order that contain list of SKUs

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

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors