Calculated Column on a temp table

[JPScotland]

I have a table that has a list of repairs that we receive everyday.  "El jefe" likes to see the data on a weekly basis so I have a column called "Week Starting Date", that I can use to split out the date.  But I am trying to calculate a weeklay average to show alongside the actual in a line graph.  

Here is what I have.  

Week Starting Date	NumberofRepairs	AverageNoOfRepairs
25/01/2021	374	1
01/02/2021	518	1
08/02/2021	516	1
15/02/2021	541	1
22/02/2021	370	1

Here is what I have but I just can't seem to get the average column to work . Basically I'd like it to run with the weeks.  

Table = 
        ADDCOLUMNS (
                       SUMMARIZE ( 
                               'Servitor Repairs General',
                                'Date'[Week Starting Date]),
                                "NumberofRepairs", CALCULATE ( DISTINCTCOUNT ('Servitor Repairs General'[Job Number as integer])),
                                "AverageNoOfRepairs", AVERAGEX ( FILTER (
                                                            ALLSELECTED ('Servitor Repairs General'),
                                                                            'Servitor Repairs General'[Week Starting Date] <= MAX ( 'Servitor Repairs General'[Week Starting Date]) ), 
                                                                                CALCULATE (DISTINCTCOUNT('Servitor Repairs General'[Job Number as integer]))
                                )
        )

Cheers,

JP

[JPScotland · Accepted solution]

This is what I did but there is perhaps a better way.  I first created a summarized table then I did the calculation based on that: -

_CalcTable Weekly Repairs = 

VAR _ALTTABLE = 
        ADDCOLUMNS (
                       SUMMARIZE ( 
                               'Repairs General',
                                'Date'[Week Starting Date]),
                                "No of Repairs", CALCULATE (DISTINCTCOUNT ('Repairs General'[Job Number as integer]))
        )
RETURN 
    _ALTTABLE

Here is a calcualtion based on that table: -

Average Weekly No of Repairs = 
//This uses the calculated table CalcTable Weekly Repairs to work out the average
 
    AVERAGEX (
                FILTER( 
                        ALLSELECTED(
                                    '_CalcTable Weekly Repairs'),   
                                    '_CalcTable Weekly Repairs'[Week Starting Date] <= MAX ('_CalcTable Weekly Repairs'[Week Starting Date])), 
            '_CalcTable Weekly Repairs'[No of Repairs]
    )<div> </div>

[amitchandak]

@JPScotland , No very clear.

But you can try like

averageX(Values('Servitor Repairs General'[Week Starting]), CALCULATE ( DISTINCTCOUNT ('Servitor Repairs General'[Job Number as integer])))

[JPScotland]

Hi amitchandak, 

Thanks for your reply.  I gave that formula a go but it calculated the same figure as the repairs and not a running weekly average.  see below. 

Cheers.

Week Starting Date	NumberofRepairs	AverageNoOfRepairs
25/01/2021	374	374
01/02/2021	518	518
08/02/2021	516	516
15/02/2021	541	541
22/02/2021	370	370