topic Re: Calculated Column on a temp table in DAX Commands and Tips

Calculated Column on a temp table

JPScotland — Thu, 04 Mar 2021 17:16:42 GMT

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,

Re: Calculated Column on a temp table

amitchandak — Fri, 05 Mar 2021 07:56:55 GMT

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])))

Re: Calculated Column on a temp table

JPScotland — Fri, 05 Mar 2021 08:10:11 GMT

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

Re: Calculated Column on a temp table

JPScotland — Tue, 09 Mar 2021 11:05:17 GMT

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>