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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
SDK0415
Frequent Visitor

Ranking of top 10 items

I have a sales data. My sales table have columns such as region, division, business unit, prod category, prod sub category, Saledate, etc.,

I would like to see top 10 items in a matrix visual or a table visual. In the table visual I have date column, division column, BU column, prod category column, Region column. I have slicers for date selection, division, BU, Cat and Sub category.
I have selected Jan month in date slicer. My table should show top 10 items based on sales broke down by each category. Totally my data has 5 prod categories. So my table should show 50 lines which is top 10 items in Jan month for each pod category.

Now if I select feb month also in slicer my table should show a result of 100 lines (50 in Jan + 50 in Feb). 


Note : I have a calendar table which is connected to the sales table through relationship( I use the date filter from the calendar table)

This is the measure I use 
Top10SalesRank =
RANKX(
ALLSELECTED('YourTable'),
CALCULATE(SUM('YourTable'[Sales])),
,
DESC,
SKIP
)
This filters my data into top 10 items based on ranking but what i need to see is 100 lines instead of 10 lines when my date slicer selecton is Jan & feb broke down for each of the category. How do I modify this measure.?

 

1 ACCEPTED SOLUTION
rajendraongole1
Super User
Super User

Hi @SDK0415 - Please try the below measure and it take into account the context of each product category and the selected date range:

 

Top10SalesRank =
RANKX(
FILTER(
ALLSELECTED('YourTable'),
'YourTable'[ProdCategory] = MAX('YourTable'[ProdCategory]) &&
'YourTable'[Saledate] IN VALUES('Calendar'[Date])
),
[TotalSales],
,
DESC,
DENSE
)

 

Create another measure to filter the top 10 items use this measure in your chart at visual level filter:

 

IsTop10 = IF([Top10SalesRank] <= 10, 1, 0)

 

Set this measure it to show only items where IsTop10 is 1.

 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

1 REPLY 1
rajendraongole1
Super User
Super User

Hi @SDK0415 - Please try the below measure and it take into account the context of each product category and the selected date range:

 

Top10SalesRank =
RANKX(
FILTER(
ALLSELECTED('YourTable'),
'YourTable'[ProdCategory] = MAX('YourTable'[ProdCategory]) &&
'YourTable'[Saledate] IN VALUES('Calendar'[Date])
),
[TotalSales],
,
DESC,
DENSE
)

 

Create another measure to filter the top 10 items use this measure in your chart at visual level filter:

 

IsTop10 = IF([Top10SalesRank] <= 10, 1, 0)

 

Set this measure it to show only items where IsTop10 is 1.

 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors