Average of sum of month values in Quarter

[ssk_1984]

Hi Team,

Pleaes help to write a measure to get the Average of sum of month values in Quarter

I have the below data, that to sum by month Quarter and final average out with the sumup values.

Data							Output 1
Project ID	Emp ID	Total Utilization	Year	Month	Quarter		Result Should be	Month	Sum of total_FTE	Average between sum of Total_FTE in Q1
1	23	0.5	2023	1	Q1		Q1	1	2.3	1.9
1	34	0.8	2023	1	Q1			2	2
1	45	1	2023	1	Q1			3	1.3
2	23	1	2023	2	Q1		Q2	4	2	2.0
2	34	1	2023	2	Q1			5	2
2	56	0.5	2023	3	Q1			6	2
2	78	0.8	2023	3	Q1
1	23	1	2023	4	Q1		Output 2
1	34	1	2023	4	Q1		Q1	1.9
1	45	1	2023	5	Q1		Q2	2
2	23	1	2023	5	Q1
2	34	1	2023	6	Q1
2	56	1	2023	6	Q1

[FreemanZ]

hi @ssk_1984 

try to plot a visual with quarter column and a measure like:

Measure = 
AVERAGEX(
    SUMMARIZE(
        data,
        data[Year],
        data[Quarter]
    ),
    CALCULATE(SUM(data[TotalUtilization]))/3
)

it worked like:

[Mahesh0016]

@ssk_1984 

Quater AVG =

ROUNDUP (

    CALCULATE (

        [TotalUtilization],

        ALLEXCEPT ( 'Quater AVG', 'Quater AVG'[Quarter] )

    )

        / CALCULATE (

            DISTINCTCOUNT ( 'Quater AVG'[Month] ),

            ALLEXCEPT ( 'Quater AVG', 'Quater AVG'[Quarter] )

        ),

    1

)
############

TotalUtilization = SUM('Quater AVG'[Total Utilization])

[FreemanZ · Accepted solution]

hi @ssk_1984 

try like:

Meaure =

VAR _table=

ADDCOLUMNS(

    SUMMARIZE(

        data,

        data[Year],

        data[Quarter],

        data[Month]

    ),

    "Utilization",

     CALCULATE(SUM(data[TotalUtilization]))

)

RETURN

AVERAGEX(

    SUMMARIZE(

        _table,

        data[Year],

        data[Quarter]

    ),

    [Utilization]

)