Forum Discussion
Cannot get total to show with RANKX using variables
Hi,
I create a measure calculating RANKX in a variable and then created another variable that limits the items returned only the Top 5 based on their rank (see 1st image below). This works fine and correctly returns the top 5 products and their sales. However, the total of these products is blank (when it should be the sum of the Top 5 products (see 2nd image below).
I did already find a different way to correctly get what I need but for my own knowedge, would someone mind telling me:
- Why this is returning a blank total and not the sum of the Top 5 products
- How to correct the DAX to return the correct total (sum of the Top 5 products)
Thank you!
(1st Image):
(2nd Image):
2 Replies
You need to calculate RankProd across the entire collection of rows in the Total area. This needs to be a table variable that list all the ranks. In case of a single row you can filter by the row title, but in case of the Totals row you need to filter by the rank, and then do the sumx.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi,
Thank you for the reply. I don't quite understand what you are suggesting so would you mind showing me? I attached the link to the .pbix file so you can download and see the (simple) data set.
https://drive.google.com/file/d/1rfaAU3v8Nuq_CxmsUkunXKm3iHOOn5S2/view?usp=share_link
To answer your question, the "Total" that I am expecting is $588,594.25 (please see image below that I manually created in excel), which is the sum of the top 5 products (i.e. - the products with the 5 largest sales).
Thanks!
