TOPN FUNCTION TO SHOW THE 'N VALUE'

[braga_felipe]

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.

[v-yangliu-msft · Accepted solution]

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

[v-yangliu-msft]

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

[braga_felipe]

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.

[amitchandak]

@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

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[braga_felipe]

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.