cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

TOP 25 Products Sold with a Single Product

I am trying to figure out a dax formula that will return table of Top 25 Products sold with a Single Product. 

 

The idea is to be able to Drill down on a product to a page which would display a table of 25 products most commonly sold with the Product that is selected. 

 

I have a large data set of the past 3 years with ProductID, SalesDocID, and Qty sold per Sales Doc. I am thinking is a combination of TOPN and CountX or something but I am new to DAX so I am not really sure

 

Here is a sample data set

Product	Sales document	Qty
6507968	1439115	1
6507737	1491663	1
6507750	1491663	1
6500568	1562306	3
6506385	1562306	2
6506386	1562306	1
6506757	1562306	46
6507071	1562306	2
6507073	1562306	1
6508529	1562306	5
6508532	1562306	6
6508535	1562306	7
6506016	1583126	1
6500564	1650179	1
6502416	1650179	3
6502480	1650179	19
6500184	1689253	1
3001562	1696959	1
3001563	1697714	1
3001563	1706018	1
3001563	1712570	1
3001563	1714391	1
3001562	1725230	1
3001563	1725242	1
3001562	1729304	1
3001562	1760809	2
3001564	1760809	2
6501606	1775800	7
6501610	1775800	53
6501618	1775800	10
6501630	1775800	10
6501634	1775800	82
6504490	1775800	41
6504494	1775800	3
6501610	1780738	6
6501622	1780738	3
6501634	1780738	16
6504490	1780738	2
3001563	1791050	1
6506195	1792263	0
6507397	1792263	0
6507508	1792263	0
6507781	1792263	0
3001563	1799425	1
3001563	1801879	1
3001562	1804318	1
3001562	1804516	1
3 REPLIES 3
Community Support
Community Support

hi, @dice927 

You could take a look at this post, it has the same logic. 

https://community.powerbi.com/t5/Desktop/Indicate-if-among-Top-X-values/m-p/628827#M300474

https://community.powerbi.com/t5/Desktop/How-to-show-top-50-and-bottom-50-records-in-a-table/m-p/627...

 

Best Regards,

Lin

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

I beleive that I do have to use some evaluation with RANKX but how do I know provide the context of the Sales Order. 

 

You example only has ID and QTY, which only provides me with the TOP and BOTTOM performing ID's. I want to know Of all the sales orders what other products are frequently sold with the ID I have already filtered on the page

hi, @dice927 

This depends on the logic of your rank measure

You may try this formula

rank = RANKX(ALLSELECTED('Table'[Product]),CALCULATE(SUM('Table'[Qty])),,DESC)

 

 

Then use this measure as a visual level filter to filter data

If not your case, please share your expected output of the sample data.

 

Best Regards,

Lin

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

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors