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...
  • 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!
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]))