Show Bottom 5 Custoomer

[dokat]

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 it to show only the bottom 5 accounts it returns blank. What may cause this issue? Appreciate any help. Thanks

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

[amitchandak · Accepted solution]

@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 · Accepted solution]

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]

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

[dokat]

@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]

Hi,

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

Thanks.

[dokat]

@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]

@Jihwan_Kim It has confidential information not sure if its possible to share the file. Thanks

[dokat]

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

[amitchandak]

@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]

@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 · Accepted solution]

@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]

@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?

[dokat]

@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