Forum Discussion
TOPN FUNCTION TO SHOW THE 'N VALUE'
Hello everyone,
I want to create a measure to return me the N value of a ranked column but i don't no where i'm doing wrong.
For example, i want to show only the second value of 'Value_1' using the 'Rank' column as the mandatory indicator. This measure must work when i filter the different scenarios with a filter box ('Scenario' column).
Could anyone help me? Thank you so much.
Hi braga_felipe ,
Here are the steps you can follow：
1. In Power query. Add Column – Index Column – From 1.
2. Create measure.
Flag = IF( MAX('Table'[Index])=2,1,0)3. Place [Flag]in Filters, set is=1, apply filter.
4. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
5 Replies
- amitchandak
Super User
braga_felipe , if you want use rank column, you can use it filter use before filter. Of can use what if to filter that
https://docs.microsoft.com/en-us/power-bi/desktop-what-if
or what if with TOPN
Hello, thank you for your answer.
Have a another way to dont use the a rank column? I mean, use only the order that i have in the original database.
Thank you.
Hi braga_felipe ,
Here are the steps you can follow：
1. In Power query. Add Column – Index Column – From 1.
2. Create calculated column.
Flag = RANKX(FILTER(ALL('Table'),[Scenairo]=EARLIER('Table'[Scenairo])),[Index],,ASC)3. Create measure.
Rank = RANKX(FILTER(ALL('Table'),[Scenairo]=MAX('Table'[Scenairo])),CALCULATE(SUM([Index])),,ASC)4. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hello!
I understood what you did, but it is not the entire solution i am trying to do.
For example, using this measures, i want to get only the rank 2 and show the value 50.
Thank you.
Hi braga_felipe ,
Here are the steps you can follow：
1. In Power query. Add Column – Index Column – From 1.
2. Create measure.
Flag = IF( MAX('Table'[Index])=2,1,0)3. Place [Flag]in Filters, set is=1, apply filter.
4. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
