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

     

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

     

     

  • dokat's avatar
    dokat
    Post Prodigy
    4 years ago

    amitchandak It appears Rank is not returning correct values. Please see below. There are two many customers rank 1. Some of the values are 0.09,0.15 and so on. Would that make a difference?