Average won't return correct value

[AndreiK15]

 Hi,

I'm trying to get average, but the return amount is wrong. I am using the followig formulas:

Sales Margin Format = CALCULATE(SUM(Sales[Sales Margin Amt]),ALLEXCEPT(Sites,Sites[Site Type 02]))

and I get 173.61M which is correct, but when I use the following formula:

Sales Margin Format (avg) = CALCULATE(AVERAGE(Sales[Sales Margin Amt]),ALLEXCEPT(Sites,Sites[Site Type 02]))

I get 1.15 and the average should be between 110k - 140k.

Thanks!

[AndreiK15 · Accepted solution]

Hi @amitchandak, 

I need average for site level 02. Site level 02 contain (STANDARD 1, STANDARD 2, CITY 1, CITY 2 etc.). I have found the solution though, but maybe you can tell if there is something else without combing all these. What I did is:

Count Format = CALCULATE(COUNTA(Sites[Site Type 02]),ALLEXCEPT(sites,Sites[Site Type 02]))

to get count on all stores for each site type 02

then

Sales Margin Format (avg) = DIVIDE([Sales Margin Format],[Count Format])

and I get the desired result. I can combine all these using var. Don't know though why when I use average as in my first post don't get the same value.

Thanks!

[amitchandak]

@AndreiK15 , This is line level average. Are looking for site level Avg ?

Sales Margin Format (avg) = CALCULATE(AVERAGEX(values(Sites[Site]) , Calculate(Sum(Sales[Sales Margin Amt]))),ALLEXCEPT(Sites,Sites[Site Type 02]))