Forum Discussion
JPScotland
5 years agoHelper I
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 ...
- 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 _ALTTABLEHere 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>
JPScotland
5 years agoHelper I
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>