Forum Discussion

AndreiK15's avatar
AndreiK15
Icon for Helper II rankHelper II
5 years ago
Solved

Average won't return correct value

 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's avatar
    AndreiK15
    5 years ago

    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!

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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]))

  • AndreiK15's avatar
    AndreiK15
    Icon for Helper II rankHelper II
    5 years ago

    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!