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

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
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

    Jihwan_Kim Thank you for sharing the sample file. I tried it however still returning blank values. Please see below.

     

     

  • dokat's avatar
    dokat
    Post Prodigy
    4 years ago

    Jihwan_Kim majority of [WTrade/NS Shr] values are in decimals smaller than <1 like 0.09,0.15,0.24 and so on could this be an issue?

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      4 years ago

      Hi,

      Please share your sample pbix file, and then I can look into it to come up with a more accurate solution.

      Thanks.

      • dokat's avatar
        dokat
        Post Prodigy
        4 years ago

        Jihwan_Kim amitchandak I figured out the issue. It was customer naming conventions was creating duplicate values which causing Rankx asc formula to not work correctly. It is fixed now and working thank you both for your help.

  • dokat's avatar
    dokat
    Post Prodigy
    4 years ago

    Jihwan_Kim if i change asc to desc in your formula it returns top 5 customers, it appears asc function is not working.