Forum Discussion
Helper IGet the top items highest to lowest value per month while using filter
how to Get the top items from highest to lowest value per month while using filter. when i use the TOp N for 5 items its also include the filter for both table. I want to show as per below like in excel top items.
this is my sample data.
| Description | Date | loss/gain |
| Item 1 | Jan-25 | 6 |
| Item 2 | Jan-25 | 5 |
| Item 5 | Jan-25 | 4 |
| Item 3 | Jan-25 | 3 |
| Item 4 | Jan-25 | 2 |
| Item 1 | Feb-25 | 6 |
| Item 2 | Feb-25 | 5 |
| Item 5 | Feb-25 | 4 |
| Item 3 | Feb-25 | 3 |
| Item 4 | Feb-25 | 2 |
| Item 4 | Jan-25 | 1 |
| Item 4 | Feb-25 | 1 |
| Item 5 | Jan-25 | -5 |
| Item 3 | Jan-25 | -4 |
| Item 4 | Jan-25 | -3 |
| Item 1 | Jan-25 | -2 |
| Item 2 | Jan-25 | -1 |
| Item 5 | Feb-25 | -5 |
| Item 3 | Feb-25 | -4 |
| Item 4 | Feb-25 | -3 |
| Item 1 | Feb-25 | -2 |
| Item 2 | Feb-25 | -1 |
Hi jayceeb
Thank you for being part of the Microsoft Fabric Community.
Sorry for the late response.
Please do follow the below steps that might resolve your issue.
The most flexible way to show the top N items per month is to rank each item within its month and then filter the visual to that rank. Here’s how:Create a Top N parameter table (disconnected) via Enter Data, for example:
TopN
-----
3
5
10
This lets end-users choose how many items to display.
Rank items per month with a measure using RANKX:
Item Rank =
RANKX(
FILTER(
ALLSELECTED( 'YourTable' ),
'YourTable'[Date] = SELECTEDVALUE( 'YourTable'[Date] )
),
CALCULATE( SUM( 'YourTable'[Loss/Gain] ) ),
,
DESC,
DENSE
)
This evaluates each item’s total value in the currently selected month context SQLBI.
Build the Top N measure that blanks out lower-ranked items:
Show TopN Value =
IF(
[Item Rank] <= SELECTEDVALUE( TopN[TopN], 5 ),
CALCULATE( SUM( 'YourTable'[Loss/Gain] ) ),
BLANK()
)
Here SELECTEDVALUE( TopN[TopN], 5 ) pulls in the user’s N choice, defaulting to 5 if none is selected.Configure your Matrix visual:
- Place Date (e.g. Month) on Rows, Description (Item) beneath it.
- Use Show TopN Value in the Values bucket.
- The matrix will automatically show, for each month, only those items whose rank is ≤ N, sorted by the value DESC.
This pattern scales to any number of items and respects page-level or slicer filters on dates.
If this solves your issue, please give us Kudos and mark it as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.
6 Replies
- pankajnamekar25
Super User
Hello jayceeb
please try belwo solution
Item Rank =
RANKX(
ALLSELECTED('YourTableName'),
CALCULATE(SUM('YourTableName'[Loss/Gain])),
,
DESC,
DENSE
)
Create a Dynamic Top N Measure
To filter based on the rank, use a measure like:
Top N Items =
IF(
[Item Rank] <= SELECTEDVALUE('Top N Table'[Top N]),
CALCULATE(SUM('YourTableName'[Loss/Gain])),
BLANK()
)Thanks,
Pankaj Namekar | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
- Cookistador
Is it what you are trying to achieve?
If yes, you don't need a top N, you just have to use the max for one chart, and the min for the other one (after, it is just some formatting to get the look and feel you want)
This is the settings for the Max
And you just have to turn on the sort by values in the settings
jayceebthe issue is if i select the date it will filter also the other chart it will give different top 5 value . I want to show top 5 items each month from highest to lowest. the table above is only sample data and i have more items. the loss chart shows correct value but in gains shows different.
- v-csrikanth
Community Support
Hi jayceeb
Thank you for being part of the Microsoft Fabric Community.
Sorry for the late response.
Please do follow the below steps that might resolve your issue.
The most flexible way to show the top N items per month is to rank each item within its month and then filter the visual to that rank. Here’s how:Create a Top N parameter table (disconnected) via Enter Data, for example:
TopN
-----
3
5
10
This lets end-users choose how many items to display.
Rank items per month with a measure using RANKX:
Item Rank =
RANKX(
FILTER(
ALLSELECTED( 'YourTable' ),
'YourTable'[Date] = SELECTEDVALUE( 'YourTable'[Date] )
),
CALCULATE( SUM( 'YourTable'[Loss/Gain] ) ),
,
DESC,
DENSE
)
This evaluates each item’s total value in the currently selected month context SQLBI.
Build the Top N measure that blanks out lower-ranked items:
Show TopN Value =
IF(
[Item Rank] <= SELECTEDVALUE( TopN[TopN], 5 ),
CALCULATE( SUM( 'YourTable'[Loss/Gain] ) ),
BLANK()
)
Here SELECTEDVALUE( TopN[TopN], 5 ) pulls in the user’s N choice, defaulting to 5 if none is selected.Configure your Matrix visual:
- Place Date (e.g. Month) on Rows, Description (Item) beneath it.
- Use Show TopN Value in the Values bucket.
- The matrix will automatically show, for each month, only those items whose rank is ≤ N, sorted by the value DESC.
This pattern scales to any number of items and respects page-level or slicer filters on dates.
If this solves your issue, please give us Kudos and mark it as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth. - v-csrikanth
Hi jayceeb
We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth. - v-csrikanth
Hi jayceeb
I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!Best Regards,
Community Support Team _ C Srikanth.
