Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

formula to calculate weekly average

Good day,  

Wonder if you could help with this,  I am trying to create a formula to create a weekly average on PowerBI (will be honest, am a bit new to it). 

So the data i have is :


WeekNum:         Count of entries:

Week 7                    3

Week 8                    7

Week 9                    3
Week 10                  8

Week 11                  4

 

 

AVERAGE                 5
DIVIDEDBY              6

 

 

 

So currently i have a calculation to divide the sum of the amounts by the amount of weeks.  So in this case it is 30 divided by 5.
However, if I calculate the average its a more accurate result. 

 

The dataset i have has entries with Dates in them and then i derive the WEEKNM from them. 

So my question would be, what formula would I use to do this average:

 

column name in table is [WeekNum]

 

expecting something like:

AverageWeekly = CALCULATE\

(  AVERAGE 
     ( 
         COUNT(Unique[WeekNum])
     )

)


Any help would be greatly appreciated 

Need Help
Tips and Tricks
  • jppv20's avatar
    jppv20
    4 years ago

    Anonymous I think I understand the question better now. Please try this:

    Average = CALCULATE(AVERAGE('Table'[Column]),ALLEXCEPT('Table','Table'[Weeknr]))

5 Replies

  • jppv20's avatar
    jppv20
    Solution Sage
    4 years ago

    Hi Anonymous ,

     

    You can try this:

    AverageWeekly = Divide(Total,Distinctcount([WeekNum],blank())

     

    Jori

     

    If my answer helped you please mark it as a solution.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you for this jppv20 

    so will this divide the total by the amount of distinct weeks?  
    my worry is that it gives me the DividedBy result and not an average value


    apologies if it is a stupid question

     

    • jppv20's avatar
      jppv20
      Solution Sage
      4 years ago

      Anonymous Could you please provide some example data and the desired output? I'm not sure if I understand your question.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        so i want to calculate the average and not the division, if you look at my detail in the original post the dividedby value is different to the average 

        its a big dataset .

        but each ticket in the table has a DateTime column, and i derive the WeekNum from that. 
        So i want to determine average per week,  rather than divide total by amount of weeks?