# DAX formula to return text string relating to highest sum of values



## btreg (Jun 8, 2016)

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


*Country
**Product**Office**Sales
*UKShoesLeeds100UKShoesLeeds100UKShoesLeeds150UKShoesLondon200UKShoesLondon340UKShoesLondon560UKShoesBirmingham800UKShoesBirmingham900UKShoesBirmingham2000

<tbody>

 </tbody>
 
I want to produce a Pivot Table that looks like this:


*Product
**Highest Sales
**Office*
Shoes3700
Birmingham


<tbody>

 </tbody>
 
Thans 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] ) ) )*

Is there a DAX formula that will return the text string 'Birmingham', based on the fact this office has the highest sales, as established by the above formula?
Any help much appreciated.


----------



## anvg (Jun 8, 2016)

Hi
Try

```
=FIRSTNONBLANK(
    SUMMARIZE(TOPN(1, 
        SUMMARIZE('Sales','Sales'[Office], "sales amount", SUM('Sales'[Sales])),
        [sales amount], 0
        ), 'Sales'[Office]
    ),1 
)
```
But are you sure that it is always a single office with the highest sales?
Regards, Andrey.


----------



## btreg (Jun 8, 2016)

Brilliant, does exactly what I needed it to, thank you! 

I guess you mean that two (or more) offices could be tied for the highes sales value? This is possible but sufficiently unlikely that I think the analysis is still useful.


----------



## btreg (Jun 13, 2016)

A follow-up question if anyone can help....

How would I modify the above formula to return the name of the office with the 3rd highest sales, for example?


----------



## anvg (Jun 13, 2016)

Hi, btred
Ozeroth has already given the best solution of that here http://www.mrexcel.com/forum/power-bi/946642-dax-formula-return-nth-highest-sum-value.html

```
FILTER (
        VALUES ( Sales[Office] ),
        RANKX ( VALUES ( Sales[Office] ), CALCULATE ( SUM ( Sales[Sales] ) ) ) = 2
    )
```
If you are sure that it is one office with rank = 2 only else add FIRSTNONBLANK
Regards,


----------



## btreg (Jun 13, 2016)

Apologies, I didn't realise this same formula could be used in the context of providing me with the office name as a text string. 

However, I am struggling to get this to work in practice. I'm getting the error message "A table of multiple values was supplied where a single value was epxected" - could this be tied values causing the error? If so, how do I add FIRSTNONBLANK to the above? My attempt is causing an error message.


----------



## anvg (Jun 13, 2016)

> I'm getting the error message "A table of multiple values was supplied  where a single value was epxected" - could this be tied values causing  the error?


Sorry. I do not know why. My Zippyshare.com - Select by rank.xlsx example works fine.


----------



## btreg (Jun 13, 2016)

Thanks for the example - your help is much appreciated. I will compare with my own model and figure out what the problem is.


----------

