Forum Discussion
TOPN by date with other category
hey there,
I want to do a simple top 10 by date and cost as you can see below :
However I want to add a "other" column that will contain the sum of cost for all the dates that we do not display.
How can I achieve this ?
thank you in advance
Hi diouckdiouck
To create a Top N by Date visualization in Power BI that includes an "Others" category to sum the remaining data, follow these steps:
- Create a Rank measure to rank your dates based on the cost (or relevant metric).
DateRank = RANKX(ALLSELECTED('YourTable'[Date]), CALCULATE(SUM('YourTable'[Cost])), , DESC, DENSE) - Create a Category column to label the Top N dates and group the rest as "Others".
DateCategory = IF([DateRank] <= N, FORMAT('YourTable'[Date], "yyyy-mm-dd"), "Others") - Use the DateCategory in your visualization to display the Top N dates individually and aggregate the remaining dates under "Others".
If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth.- Create a Rank measure to rank your dates based on the cost (or relevant metric).
6 Replies
- Cookistador
Super User
Hello
this post explains you how to achieve that:
(I followed it a few months ago)
You can do it with the slicer, but you can achieve something similar in dax and use a numerical parameter
it works if I use a category but I want to do the TOPN by date.
- v-csrikanth
Community Support
Hi diouckdiouck
To create a Top N by Date visualization in Power BI that includes an "Others" category to sum the remaining data, follow these steps:
- Create a Rank measure to rank your dates based on the cost (or relevant metric).
DateRank = RANKX(ALLSELECTED('YourTable'[Date]), CALCULATE(SUM('YourTable'[Cost])), , DESC, DENSE) - Create a Category column to label the Top N dates and group the rest as "Others".
DateCategory = IF([DateRank] <= N, FORMAT('YourTable'[Date], "yyyy-mm-dd"), "Others") - Use the DateCategory in your visualization to display the Top N dates individually and aggregate the remaining dates under "Others".
If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth. - Create a Rank measure to rank your dates based on the cost (or relevant metric).
- v-csrikanth
Hi diouckdiouck
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!
If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth. - v-csrikanth
Hi diouckdiouck
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
Cheri Srikanth - v-csrikanth
Hi diouckdiouck
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.
