formula to calculate weekly average

[Anonymous]

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 

[Anonymous]

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]

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

[Anonymous]

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?

[jppv20 · Accepted solution]

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

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

[jppv20]

Hi @Anonymous ,

You can try this:

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

Jori

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