Forum Discussion

dokat's avatar
dokat
Post Prodigy
4 years ago
Solved

Show Bottom 5 Custoomer

Hi,   I am tryin to show bottom 5 account based on a share criteria. I am using the below code to limit the results to bottom 5. Formula shows all values when i exclude <=5 however when i include i...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    dokat , Change second have two measures

     

    Rank = RANKX(ALLSELECTED('P&L'[Customer]),('P&L'[W.Trade/NS Shr]),,ASC)

     

    Only 5= sumx(filter(Values('P&L'[Customer]) ,[Rank]<=5) ,[W.Trade/NS Shr])

     

    Hope the bottom five are not all 0

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure how your data model looks like, but I tried to create a sample pbix file like the attached file.

    In my attached file, I think your measure is working correctly.

    But, I added one more measure and please try to apply it to your model and please check if it works.

    I assume your measure shows blank when you put it into the card visualization? Because your measure does not show total value but only shows individual customer's value.

     

     

     

    Expected outcome ver2: =
CALCULATE (
    [W.Trade/NS Shr],
    KEEPFILTERS (
        TOPN ( 5, ALLSELECTED ( 'P&L'[Customer] ), [W.Trade/NS Shr], ASC )
    )
)

     

amitchandak's avatar
amitchandak
Super User
4 years ago

dokat , Use top 5

Top 5 =calculate([W.Trade/NS Shr], TOPN(5,allselected('P&L'[Customer]),[W.Trade/NS Shr],Asc), values('P&L'[Customer]))

 

or like this
sumx(Values('P&L'[Customer]) ,
if(RANKX(ALLSELECTED('P&L'[Customer]),('P&L'[W.Trade/NS Shr]),,ASC)<=5,[W.Trade/NS Shr]))

  • dokat's avatar
    dokat
    Post Prodigy
    4 years ago

    amitchandak Thank you for your response. I tried both codes bfirst one returned blank values second one returned [W.Trade/NS Shr] values for all customers. Not just the 5. Not sure what's causing it

    • amitchandak's avatar
      amitchandak
      Super User
      4 years ago

      dokat , Change second have two measures

       

      Rank = RANKX(ALLSELECTED('P&L'[Customer]),('P&L'[W.Trade/NS Shr]),,ASC)

       

      Only 5= sumx(filter(Values('P&L'[Customer]) ,[Rank]<=5) ,[W.Trade/NS Shr])

       

      Hope the bottom five are not all 0

       

      If this does not help
      Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

      • dokat's avatar
        dokat
        Post Prodigy
        4 years ago

        amitchandak Thanks for your reply. I created rank measure then the second measure. However it is not narrowing down the list to bottom 5. Please see below screenshot. Thanks