DAX formula to display biggest sum value by category

[btreg]

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

[TABLE="class: grid, width: 274"]
<colgroup><col span="2"><col><col></colgroup><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: grid, width: 146"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Product
[/TD]
[TD="align: right"]Biggest Sales
[/TD]
[/TR]
[TR]
[TD]Shoes[/TD]
[TD="align: right"]3700
[/TD]
[/TR]
</tbody>[/TABLE]

(i.e. I want the DAX formula 'Biggest Sales' to return the sum of Birmingham's sales, as Birmingham has the highest value of sales. I do not want to display the Office names in the Pivot Table).

Any help much appreciated.

 

[Ozeroth]

Hi there,

If you're looking for a formula to return the maximum value of sales by Office, this will do the trick (assuming your table is called Sales):

=
MAXX ( VALUES ( Sales[Office] ), [COLOR=#ff0000][B]CALCULATE ( SUM ( Sales[Sales] ) )[/B][/COLOR] )

If you already have a measure summing sales, you can put it in place of the red portion.

 

[btreg]

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

 

[btreg]

Superb, does exactly what I needed it to, thanks!