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:

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

[TABLE="class: cms_table_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]


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.