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
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.?
Solved! Go to Solution.
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!!
Proud to be a 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!!
Proud to be a Super User! | |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
34 | |
14 | |
12 | |
12 | |
11 |