Forum Discussion

HDBI's avatar
HDBI
Regular Visitor
3 years ago

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 ...
Need Help
lbendlin's avatar
lbendlin
Super User
3 years ago

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

  • HDBI's avatar
    HDBI
    Regular Visitor
    3 years ago

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

     

    Thanks!