Calculate 2end et 3th highest value with measur

[Anonymous]

Hello family ;
I want help to display in a measure the second great value and the third also

I have 2 measure  of calculations I transformed the figure and absolute value
as shown in the photos below

thank you in advance

[Icey · Accepted solution]

Hi @Anonymous ,

I create a simple example that you can refer to:

1. Table:

2. Measures.

Percent =
CALCULATE ( SUM ( 'Table'[Value1] ) - SUM ( 'Table'[Value2] ) )
    / SUM ( 'Table'[Value2] ) * 100

ABS Percent = ABS ( [Percent] )

3. Then I create a Rank measure.

Rank = RANKX ( ALLSELECTED ( 'Table' ), [ABS Percent],, DESC, DENSE )

4. Create measure to show 1st, 2nd, 3nd highest value.

1st highest value = CALCULATE([ABS Percent],FILTER('Table',[Rank]=1))

2nd highest value = CALCULATE([ABS Percent],FILTER('Table',[Rank]=2))

3rd highest value = CALCULATE([ABS Percent],FILTER('Table',[Rank]=3))

For more details, please check the attached PBIX file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Not Sure I got completely. But seems like Rank can help

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

[Anonymous]

RANKX allows you to sort your table based on a measure that you would like

So I suggest to create a virtual table with a SUMMARIZE, representing in memory your table as in the visual, then adding a RANKX. It will add a column with numbers 1 to N where 1 is the first, 2 is the second etc. 
Then with a FILTER you can choose the 1st, 2nd etc.

[Anonymous]

Exactly yes; @Anonymous 
I have already made the rank but what I really want is to display it in a measure like the max function which takes the 1st I want to know is what we can do a compound function to have a measure displays the 2end and 3thd value 

[Icey · Accepted solution]

Hi @Anonymous ,

I create a simple example that you can refer to:

1. Table:

2. Measures.

Percent =
CALCULATE ( SUM ( 'Table'[Value1] ) - SUM ( 'Table'[Value2] ) )
    / SUM ( 'Table'[Value2] ) * 100

ABS Percent = ABS ( [Percent] )

3. Then I create a Rank measure.

Rank = RANKX ( ALLSELECTED ( 'Table' ), [ABS Percent],, DESC, DENSE )

4. Create measure to show 1st, 2nd, 3nd highest value.

1st highest value = CALCULATE([ABS Percent],FILTER('Table',[Rank]=1))

2nd highest value = CALCULATE([ABS Percent],FILTER('Table',[Rank]=2))

3rd highest value = CALCULATE([ABS Percent],FILTER('Table',[Rank]=3))

For more details, please check the attached PBIX file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Icey  

thank you for your solution
it's professional work on your part

I have a problem if we want to add the date for example the rank does not change

for example :

calculate the 1st 2nd 3rd of the date Feb 15 ----> March 3
calculate the 1st 2nd 3rd of the date Jan 10 ----> Apr 1

it means when you choose a date range the rank changes according to date range 

[Icey · Accepted solution]

Hi @Anonymous ,

If you want the Rank value changed by the Date range you selected, the measure above can meet your requirement. Because I used "ALLSELECTED" in the expression.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

 @Icey  I am proud to be part of the Power Bi family
thank you it's too professional especially the aspect of sharing and help

I have a little problem with the rank when we have a null value it considers 0 as a 1st rank

how not to take null values

Mesures : 

resultat : 

[Icey]

Hi @Anonymous ,

If you want to show the records with null values, you can change your Measure like so:

Rank =
IF (
    [ABS Percent] <> 0,-----------You can replace 0 with BLANK() in your scenario.
    RANKX (
        FILTER ( ALLSELECTED ( 'Table' ), [ABS Percent] <> 0 ),----You can replace 0 with BLANK() in your scenario.
        [ABS Percent],
        ,
        ASC,
        DENSE
    )
)

If you don't want to show the records with null value, just try something like below to filter the visual.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Icey  

Thank you sir we are too good for the first exercise
that's another one or I want the row to start with 1 but as you see in the picture it starts with 1 in a null value 

thank you 

[Icey]

Hi @Anonymous ,

If the screenshot below is like what you want, please try the expression above.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.