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>
amitchandak
5 years agoSuper User
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])))
- JPScotland5 years agoHelper I
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