Forum Discussion

gbarr12345's avatar
gbarr12345
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

Bottom 5 Sales by Market Measure

Hi there,

 

I'm looking to create a measure to get the bottom 5 sales by Market without needing the use of slicers/ filters.

 

Is it possible to do this with a measure as I can do the top 5 but I'm struggling to do the bottom 5.

 

I have attached a sample PBIX and my sample Excel data also below.

 

Any help would be appreciated!

 

PBIX - https://drive.google.com/file/d/1k2h-CepPaYARqsROgRIkAlv60z2khZKP/view?usp=drive_link

 

Excel data - https://docs.google.com/spreadsheets/d/1FNQaZ4Eom6-FqzLvoCZl8t_mOPqXIOHA/edit?usp=drive_link&ouid=116472822422825040168&rtpof=true&sd=true

 

 

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi gbarr12345 
    You can use a measure :

    Bottom_5 =
    Var
    Rank_market = RANKX(ALLSELECTED('Dimension_Market Table'[Market]),[total_sales],,ASC,Dense)
    RETURN
    IF(Rank_market>0 &&Rank_market<6,[total_sales],BLANK())

     

    pbix is attached
     

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

     

9 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Yo have just shared the download link of th e PBi file without showing the problematic visual/calculation.  Build your visual and clearly show the problem there.

  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    2 years ago

    Hi gbarr12345 
    You can use a measure :

    Bottom_5 =
    Var
    Rank_market = RANKX(ALLSELECTED('Dimension_Market Table'[Market]),[total_sales],,ASC,Dense)
    RETURN
    IF(Rank_market>0 &&Rank_market<6,[total_sales],BLANK())

     

    pbix is attached
     

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

     

    • gbarr12345's avatar
      gbarr12345
      Icon for Post Prodigy rankPost Prodigy
      2 years ago

      Hi Rita,

       

      Thank you for your response.

       

      I tried your measure and it's showing as blank for me. Any idea why this is happening?

       

       

      • Ritaf1983's avatar
        Ritaf1983
        Icon for Super User rankSuper User
        2 years ago

        I can't know without seeing the pbix.
        Please download the file that I attached in the first reaction and follow my steps.

        If it still doesn't work, attach yours with the problem....

        If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    If you can to the Top 5 then you can do the Bottom 5 too - just revert the sort order.