Line Graph Aggregate instead of Average

[El_Bastien]

Hi, I'm having issues with a visual. I want the average headcount by employee type by a quarter displayed on a line graph. This is the formula I have to get the average.

Avg Headcount = 
    AVERAGEX(
        VALUES('FY25'[Employee Type]),
        CALCULATE(SUM('FY25'[Count]))
    )

 
When adding that to the line graph it displays this.

The line graph format is the one I'm looking for, showing each quarter by month. However, the values do not give the average but the aggregation. Any ideas on how to do this?

To give you some extra details, these are the Tables and Columns I'm using

Table 1
- Employee Name

- Employee Type

- Count (Just a 1 assigned to each column)
- Period (Month Starting Date)

Calendar

Calendar = 

--Inputs--
VAR WeekStartsOn = "Mon"
VAR FiscalStartMonth = 7 

--NOTE: Calendar week starts from Monday

--Calculation--
RETURN
    ADDCOLUMNS (
        CALENDARAUTO ( FiscalStartMonth - 1 ),
        "MIndex", MONTH ( [Date] ),
        "FiscalMIndex", MONTH ( EDATE ( [Date], - FiscalStartMonth + 1 ) ),
        "CalMonth", FORMAT ( [Date], "mmm" ),
        "CalQtr", "Q"
            & CEILING ( MONTH ( [Date] ), FiscalStartMonth - 1 ) / ( FiscalStartMonth - 1 ),
        "CalYear", YEAR ( [Date] ),
        "Fiscal Week",
        VAR FiscalFirstDay =
            IF (
                MONTH ( [Date] ) < FiscalStartMonth,
                DATE ( YEAR ( [Date] ) - 1, FiscalStartMonth, 1 ),
                DATE ( YEAR ( [Date] ), FiscalStartMonth, 1 )
            )
        VAR FilteredTableCount =
            COUNTROWS (
                FILTER (
                    SELECTCOLUMNS ( GENERATESERIES ( FiscalFirstDay, [Date] ), "Dates", [Value] ),
                    FORMAT ( [Dates], "ddd" ) = WeekStartsOn
                )
            )
        VAR WeekNos =
            IF (
                FORMAT ( FiscalFirstDay, "ddd" ) <> WeekStartsOn,
                FilteredTableCount + 1,
                FilteredTableCount
            )
        RETURN
            "Week " & WeekNos,
        "Fiscal Qtr", "Q"
            & CEILING ( MONTH ( EDATE ( [Date], - FiscalStartMonth + 1 ) ), 3 ) / 3,
        "Fiscal Year",
        VAR CY =
            RIGHT ( YEAR ( [Date] ), 2 )
        VAR NY =
            RIGHT ( YEAR ( [Date] ) + 1, 2 )
        VAR PY =
            RIGHT ( YEAR ( [Date] ) - 1, 2 )
        VAR FinYear =
            IF ( MONTH ( [Date] ) > ( FiscalStartMonth - 1 ), NY , CY )
        RETURN
            FinYear,
        "CalWeekNo", WEEKNUM ( [Date], 2 ),
        "Weekend/Working", IF ( WEEKDAY ( [Date], 2 ) > 5, "Weekend", "Working" ),
        "Day", FORMAT ( [Date], "ddd" )
    )

 
Thanks in advance for the help!

[Anonymous]

Hi @El_Bastien  ,

Could you share the sample data and the expected outputs?

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Best Regards,

Wearsky

[El_Bastien]

Employee	Category	Month	Period	Count
904	GT	April	01/04/24	1
54	A	April	01/04/24	1
363	C	May	01/05/24	1
1	D	May	01/05/24	1
54	A	May	01/05/24	1
591	C	June	01/06/24	1
1	D	June	01/06/24	1
54	A	June	01/06/24	1
489	C	July	01/07/24	1
1	D	July	01/07/24	1
54	A	July	01/07/24	1
591	C	August	01/08/24	1
1	D	August	01/08/24	1
54	A	August	01/08/24	1
591	C	September	01/09/24	1
1	D	September	01/09/24	1
244	D	September	01/09/24	1
54	A	September	01/09/24	1
1	D	October	01/10/24	1
54	A	October	01/10/24	1

Here is the sample data.

Ignore the "Employee" column as it is unimportant to the problem.

The expected outputs are:

Average Quarterly Headcount by Category. In my first message, I inserted a picture of what the line chart has to be like. The problem with that is that PBI aggregates each quarter, and I need the average. for example if: M1=100, M2=105, M3=110, the Q1avg = 105. The average must be shown when displaying the year and the quarter (as shown in the picture). If necessary, we can add the month as the lowest level, but that is optional.

Thank you so much! @Anonymous