cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Richard_Halsall
Helper I
Helper I

Aggregate Daily data to Monthly with Average

Hi could somebody help me with this requirement,

 

I have this simple table of data with a relationship to a date table 

 

Date__cOpportunity__cTechnicianRoleTechniciansGap
18/09/20220063H000008N3r9QACSiemens Level 522
17/09/20220063H000008N3r9QACSiemens Level 522
16/09/20220063H000008N3r9QACSiemens Level 522
15/09/20220063H000008N3r9QACSiemens Level 522
14/09/20220063H000008N3r9QACSiemens Level 522
13/09/20220063H000008N3r9QACSiemens Level 522
12/09/20220063H000008N3r9QACSiemens Level 522
11/09/20220063H000008N3r9QACSiemens Level 522
10/09/20220063H000008N3r9QACSiemens Level 522
09/09/20220063H000008N3r9QACSiemens Level 522
08/09/20220063H000008N3r9QACSiemens Level 522
07/09/20220063H000008N3r9QACSiemens Level 522
06/09/20220063H000008N3r9QACSiemens Level 522
05/09/20220063H000008N3r9QACSiemens Level 522
18/09/20220063H000008N3qbQACSiemens Level 511
17/09/20220063H000008N3qbQACSiemens Level 511
16/09/20220063H000008N3qbQACSiemens Level 511
15/09/20220063H000008N3qbQACSiemens Level 511
14/09/20220063H000008N3qbQACSiemens Level 511
13/09/20220063H000008N3qbQACSiemens Level 511
12/09/20220063H000008N3qbQACSiemens Level 511
11/09/20220063H000008N3qbQACSiemens Level 511
10/09/20220063H000008N3qbQACSiemens Level 511
09/09/20220063H000008N3qbQACSiemens Level 511
08/09/20220063H000008N3qbQACSiemens Level 511
07/09/20220063H000008N3qbQACSiemens Level 511
06/09/20220063H000008N3qbQACSiemens Level 511
05/09/20220063H000008N3qbQACSiemens Level 511

 

I am attempting to write a DAX formula to aggregate the above table to a monthly level grouped by Opportunity & TechnicianRole whilst averaging Technicians & Gap so the result would be:

 

Date__cOpportunity__cTechnicianRoleTechniciansGap
Sept 220063H000008N3r9QACSiemens Level 522
Sept 220063H000008N3qbQACSiemens Level 511

 

I would use this DAX measure a line graph. Any advice would be appreciated. Thanks

1 ACCEPTED SOLUTION
v-yadongf-msft
Community Support
Community Support

Hi @Richard_Halsall 

 

1.You can add a column called month like this:

vyadongfmsft_0-1659001134052.png

 

2.Then create two measures like below:

vyadongfmsft_1-1659001134053.png

vyadongfmsft_2-1659001134053.png

Technicans = AVERAGE('fact'[Technicians])
Gap = AVERAGE('fact'[Gap])

 

3.Then on the report page, create a new table visual:

vyadongfmsft_3-1659001134055.png

You can also create line chart by using the measures you created.

vyadongfmsft_4-1659001134056.png

Best Regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yadongf-msft
Community Support
Community Support

Hi @Richard_Halsall 

 

1.You can add a column called month like this:

vyadongfmsft_0-1659001134052.png

 

2.Then create two measures like below:

vyadongfmsft_1-1659001134053.png

vyadongfmsft_2-1659001134053.png

Technicans = AVERAGE('fact'[Technicians])
Gap = AVERAGE('fact'[Gap])

 

3.Then on the report page, create a new table visual:

vyadongfmsft_3-1659001134055.png

You can also create line chart by using the measures you created.

vyadongfmsft_4-1659001134056.png

Best Regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

tamerj1
Super User
Super User

Hi @Richard_Halsall 

you can slice by month and opportunity and simply USE MAX or MIN or SELECTEDVALUE to retrieve the respective comulm value

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors