DAX to write average line of teams by month

[xlitol]

Hi there ! -  a quick qn. i have the below table which i am using a "line chart" to plot the trend line. X-axis the is YYYY-MM column, and Y-AXIS is SUM(WARNINGS), and legend is TEAM. So basically a trend of total warnings of each team by month. 

I would like a DAX to create a average trending line by month (i.e. no horizontal but an average of total warning by month). What is a quick DAX code to use ?

Team	Staff_ID	Warnings	yyyy-mm
A	111	1	2023-01
A	111	1	2023-01
A	122	2	2023-01
A	122	1	2023-02
B	211	1	2023-01
B	211	5	2023-02
B	211	3	2023-04
C	311	1	2023-01
C	322	3	2023-02

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Warnings avg: =
VAR _maxdatefacttable =
    CALCULATE ( MAX ( Data[yyyy-mm] ), REMOVEFILTERS ( 'Calendar' ) )
RETURN
    IF (
        MAX ( 'Calendar'[Year-Month sort] ) <= EOMONTH ( _maxdatefacttable, 0 ),
        AVERAGE ( Data[Warnings] ) + 0
    )

[Ritaf1983 · Accepted solution]

Hi @xlitol 

If I understand you correctly, and the desired result is like in picture

The dax is :

MonthAverage = AVERAGEX(values('Table'[Month]),SUM('Table'[Warnings])/DISTINCTCOUNT('Table'[Team]))

But you'll need to make a separate measure for each line of each team
Here's an example:

WarningsA = CALCULATE(sum('Table'[Warnings]),'Table'[Team]="a")

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

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Warnings avg: =
VAR _maxdatefacttable =
    CALCULATE ( MAX ( Data[yyyy-mm] ), REMOVEFILTERS ( 'Calendar' ) )
RETURN
    IF (
        MAX ( 'Calendar'[Year-Month sort] ) <= EOMONTH ( _maxdatefacttable, 0 ),
        AVERAGE ( Data[Warnings] ) + 0
    )