Forum Discussion
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])
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.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 ) ) )
12 Replies
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.
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 Thank you for sharing the sample file. I tried it however still returning blank values. Please see below.
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.
Jihwan_Kim if i change asc to desc in your formula it returns top 5 customers, it appears asc function is not working.
