# DAX formula to return the nth highest sum value



## btreg (Jun 11, 2016)

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


*Country
**Product**Office**Sales
*UKShoesLeeds100UKShoes
Leeds100UKShoesLeeds150UKShoesLondon200UKShoesLondon340UKShoesLondon560UKShoesBirmingham800UKShoesBirmingham900UKShoesBirmingham2000

<tbody>

 </tbody>

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


*Product
**Highest Sales
**Office*
Shoes3700
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 (Jun 11, 2016)

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


----------



## btreg (Jun 11, 2016)

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 (Jun 11, 2016)

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 (Jun 12, 2016)

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!


----------

