DAX formula to return the nth highest sum value

[btreg]

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

[TABLE="class: grid, width: 274"]
<tbody>[TR]
[TD]Country
[/TD]
[TD]Product[/TD]
[TD]Office[/TD]
[TD="align: right"]Sales
[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]Shoes[/TD]
[TD]Leeds[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]Shoes
[/TD]
[TD]Leeds[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]Shoes[/TD]
[TD]Leeds[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]Shoes[/TD]
[TD]London[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]Shoes[/TD]
[TD]London[/TD]
[TD="align: right"]340[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]Shoes[/TD]
[TD]London[/TD]
[TD="align: right"]560[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]Shoes[/TD]
[TD]Birmingham[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]Shoes[/TD]
[TD]Birmingham[/TD]
[TD="align: right"]900[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]Shoes[/TD]
[TD]Birmingham[/TD]
[TD="align: right"]2000[/TD]
[/TR]
</tbody>[/TABLE]



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

[TABLE="class: grid, width: 146"]
<tbody>[TR]
[TD]Product
[/TD]
[TD="align: right"]Highest Sales
[/TD]
[TD]Office
[/TD]
[/TR]
[TR]
[TD]Shoes[/TD]
[TD="align: right"]3700
[/TD]
[TD="align: right"]Birmingham
[/TD]
[/TR]
</tbody>[/TABLE]



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!