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

[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 want to produce a Pivot Table that looks like this:

Product	Highest Sales	Office
Shoes	3700	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]

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]

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]

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]

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]

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]

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]

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