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 ...
  • JPScotland's avatar
    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>