DAX formula to display biggest sum value by category

[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

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

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

Product	Biggest Sales
Shoes	3700

<colgroup><col><col></colgroup><tbody>
</tbody>

(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!