Forum Discussion

JPScotland's avatar
JPScotland
Helper I
5 years ago
Solved

Calculated Column on a temp table

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 DateNumberofRepairsAverageNoOfRepairs

25/01/2021

3741
01/02/20215181
08/02/20215161
15/02/20215411
22/02/20213701

 

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's avatar
    JPScotland
    5 years ago

    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>

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      JPScotland
      Helper I
      5 years ago

      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 DateNumberofRepairsAverageNoOfRepairs

      25/01/2021

      		374374
      01/02/2021518518
      08/02/2021516516
      15/02/2021541541
      22/02/2021370370
  • JPScotland's avatar
    JPScotland
    Helper I
    5 years ago

    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>