Average Calculated Fields

[seancsn]

I have a table in Powerpivot with columns: Country, County, Town, Product name, Sales, number of Products sold.

How can I create a calculated field to show average sales and number of products sold - so that when a specific product for a town is selected in a powerpivot table the sales can be compared for that product compared with the average for the County and Country?

Thanks in advance

Sean

 

[scottsen]

Say you have a typical measure:
AvgSold := AVERAGE(SalesTable[NumSold])

So, that works regardless of context. If you put Country on rows, it would be evaluated separately for each Country and "just work". Filtering down to 1 product... same thing. It "just works".

Where things are "interesting" is the "I want the Avg across ALL towns in the county", and... I'm stupid tired, but hopefully get this right...

AvgAllTowns := CALCULATE([AvgSold], ALL(SalesTable[Town]))

Which says "do that same calc, but REMOVE any filter context you have on Town. So, when evaluated in the country of a County... it would be the county-wide average.

(Note, I am pretending you have just 1 table, quite possible you have a geography table, and you would actually put THAT column in the ALL()).

 

[seancsn]

Thank you so much Scott for responding to my request. I will try this today and let you know how I get on.