Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dokat
Post Prodigy
Post Prodigy

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

 

2 ACCEPTED SOLUTIONS

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

View solution in original post

Jihwan_Kim
Super User
Super User

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.

 

Picture2.png

 

 

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

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


View solution in original post

12 REPLIES 12
Jihwan_Kim
Super User
Super User

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.

 

Picture2.png

 

 

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

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


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

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

Hi,

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

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


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

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

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

 

dokat_0-1648182027154.png

 

amitchandak
Super User
Super User

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

@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

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

@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_0-1648182420990.png

 

@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_1-1648182200668.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.