DAX - calculating weekly average over all present weeks

[skaratela]

Hi all,

I want to calculate a weekly average and have tried many tutorials online and have had no joy so far...  So the issue is with the filter context (i believe) as I want to show the totals for a week and next to the total I want to show the weekly average.

So here is my raw data:

So what I want to calculate is the weekly average, but first I aggregate by week:

And then I add in the average for the weeks I have (it will be more than 5 in the real example), but for this problem its (3 + 3 + 1 + 2 + 1) / 5 = 1.8 :

The problem is I cannot set the 1.8 average to display (as I have in the table above).  Here is my code so far:

TotalWklyAvg:=calculate(SUMX(all(vw_ActivityTable),([TotalCountMeasure])) ) / DISTINCTCOUNT(time[Week])

The TotalCountMeasure is the sum of my counts

The time[week] is the weeks

Your help would be most appreciated!

Regards,

S

[v-yulgu-msft]

Hi @skaratela,

Please modify your formula as :

TotalWklyAvg:=calculate(SUMX(all(vw_ActivityTable),([TotalCountMeasure])) ) / CALCULATE(DISTINCTCOUNT(time[Week]),ALL(time))

Best regards,
Yuliana Gu 

[skaratela]

Hi @v-yulgu-msft

Sorry for the late reply.  I actuallt solved this problem by creating calculated tables.  So the first calculated table worked out the weekly total and then the second calculated table worked out the average of the weeks.  I then joined this back to my main table.

When I re-visit this project I will try and see if your solution works! 🙂

Thanks for your reply.