Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
This is the most efficient measure I can think of (I've called your table Orders):
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
Hi,
How is the base data arranged? Paste the data here.
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.
Regards,
Lydia
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.
OrderNo | SKU |
881133 | 2957 |
881133 | 5649 |
881713 | 3524 |
881937 | 92041 |
881937 | 5649 |
881955 | 2957 |
881955 | 5649 |
882021 | 39546 |
882021 | 39522 |
882021 | 5659 |
882022 | 39522 |
882132 | 39522 |
883177 | 39522 |
884250 | 2957 |
884306 | 3524 |
884586 | 2957 |
884586 | 5649 |
885818 | 6607 |
887811 | 84310 |
889323 | 6607 |
890751 | 21378 |
891036 | 16064 |
891473 | 2957 |
892230 | 57736 |
892778 | 16064 |
892873 | 16064 |
892875 | 16064 |
892997 | 77421 |
893254 | 39542 |
893262 | 16064 |
893319 | 16064 |
893460 | 2957 |
893460 | 5649 |
893540 | 77421 |
893846 | 90777 |
893853 | 66298 |
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.
This is the most efficient measure I can think of (I've called your table Orders):
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
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 🙂
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |