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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
HDBI
Regular Visitor

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):

HDBI_0-1683220818126.png

 

(2nd Image):

HDBI_1-1683220868325.png

 

2 REPLIES 2
lbendlin
Super User
Super User

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-...
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/1447523

Hi,

 

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).  

 

HDBI_0-1683596172551.png

Thanks!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Kudoed Authors