TOP 5 Sales as benchmark for other metrics

[dzaba]

Hi I would like to prepare banchmark which is basing on top5 sales cities

My DAX formula for Top 5 ProductSales Cities looks like below:

TOP 5 =

VAR

Gettop5reg= VALUES( 'Table1'[City] )

RETURN

CALCULATE( [totalSales],

TOPN( 5,

ALL( 'Table1'[City] ),

[totalSales] ),

Gettop5reg

)

now I would like to calculate average of ServiceSales only for this TOP5 ProductsSales cities and use as a benchmark for ServiceSales for specific region.

Country	City	ProductSales	ServiceSales
UK	London	15550	5465
UK	Manchester	25568	3541
UK	Liverpool	10005	2516
UK	Bristol	8955	1518
UK	Coventry	47115	2134
UK	Leeds	56555	1563
ES	Barcelona	111255	1146
ES	Madrit	95674	9998
ES	Toledo	11155	6652
ES	Bilbao	35963	13215
ES	Sevilla	54589	5444

[dzaba]

this works fine, thank you very much, but I have other question to that: If I would like to put this measure to the table, to have average of this 5 regions in all rows? 

like in every row in second column I would like to see 44,53

[Ashish_Mathur]

You are welcome.  I can try but not sure of whether i will be able to solve it or not.  Share the link from where i can download your PBI file.

[dzaba]

Hi, here is the file, thanks in advance! 🙂

https://1drv.ms/u/s!AmHaMHGdkcnBatiQFJMfYt2P0qQ?e=uoaWRX 

[Ashish_Mathur]

Hi,

This measure works

Measure = COALESCE([Average TOP 5 service sales],CALCULATE([Average TOP 5 service sales],ALL('Table')))

Hope this helps.

[dzaba]

Thanks, almost what I need 🙂 

I remove COALESCE

and now i have the benchmark for gauge chart, but how to divide it per country? now I have the average for all, I need separate for UK and ES

[dzaba]

Ok, I update the function to:

Measure = CALCULATE([Average TOP 3 service sales],ALL('Table'[City]))

In tabel looks ok, but when I'm using it on charts always took the average of total

[Ashish_Mathur]

Those images are way too small.  There is nothing i can understand.

[Ashish_Mathur · Accepted solution]

Hi,

Does this measure help

Average TOP 5 service sales=

VAR

Gettop5reg= VALUES( 'Table1'[City] )

RETURN

CALCULATE(average( 'Table1'[ServiceSales]),

TOPN( 5,

ALL( 'Table1'[City] ),

[totalSales] ),

Gettop5reg

)