DAX formula to return the nth highest sum value

[btreg]

I have a table of data, an extract of which looks like this:

Country	Product	Office	Sales
UK	Shoes	Leeds	100
UK	Shoes	Leeds	100
UK	Shoes	Leeds	150
UK	Shoes	London	200
UK	Shoes	London	340
UK	Shoes	London	560
UK	Shoes	Birmingham	800
UK	Shoes	Birmingham	900
UK	Shoes	Birmingham	2000

<tbody>
</tbody>

I wanted to produce a Pivot Table that looks like this:

Product	Highest Sales	Office
Shoes	3700	Birmingham

<tbody>
</tbody>

Thanks to help from an earlier thread, I am using the following DAX formula to return the value of the Birmingham office's sales, as Birmingham has the highest sales:

=MAXX ( VALUES ( Sales[Office] ), CALCULATE ( SUM ( Sales[Sales] ) ) )

This works really well. However, I would now like to be able to return the total sales made by the office with the 2nd highest sales. Is there a DAX formula that will do this for me?

Eventually I want to give the user the ability to choose a rank (1st, 2nd, 3rd, etc.) and see the sales value for the relevant office.

Any help much appreciated.

 

[Dan80]

Can you use a rank formula?
https://msdn.microsoft.com/en-us/library/gg492185.aspx

 

[btreg]

I was under the impression that a rank formula would TELL me the rank of a value among a list of vales, rather than let me specify a rank and return the associated value. Happy to be corrected if I'm wrong though!

 

[Ozeroth]

Hi btreg & Dan,

Two general ideas I can think of, using TOPN or RANKX/FILTER.
You may need to modify to handle ties appropriately & handle cases where the desired rank is greater than the number of Offices.

If there are no ties between Offices then the below formulas should behave the same.

1. This formula will take the top 2 Offices in descending order and take the minimum sales from the Offices in that list. Any ties at the bottom of the list will have the same sales as the 2nd ranked Office.

=
MINX (
    TOPN ( [COLOR=#ff0000][B]2[/B][/COLOR], VALUES ( Sales[Office] ), CALCULATE ( SUM ( Sales[Sales] ) ) ),
    CALCULATE ( SUM ( Sales[Sales] ) )
)

2. This formula filters the Offices to find the one ranked 2. If that rank doesn't exist (due to ties) it will return blank. You could add the 'dense' argument to RANKX, but this would change the behaviour (e.g. if your Offices' sales were 1000,1000,500,500 it would return 500).

=
MINX (
    FILTER (
        VALUES ( Sales[Office] ),
        RANKX ( VALUES ( Sales[Office] ), CALCULATE ( SUM ( Sales[Sales] ) ) ) =[COLOR=#ff0000][B] 2[/B][/COLOR]
    ),
    CALCULATE ( SUM ( Sales[Sales] ) )
)

 

[btreg]

Thanks Ozeroth for the options and explanations. I've gone with the first option and it's working exactly as I had hoped. Your help is much appreciated!