Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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,
JP
Solved! Go to Solution.
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>
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>
@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])))
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 |
User | Count |
---|---|
53 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
80 | |
57 | |
40 | |
19 | |
10 |