Forum Discussion

skaratela's avatar
skaratela
Frequent Visitor
9 years ago

DAX - calculating weekly average over all present weeks

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

 

 

 

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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's avatar
      skaratela
      Frequent Visitor
      9 years ago

      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.