How to calculate average based on a calculated measure

[Anonymous]

Hello everyone, 

I have a problem. Here is my measure, it's a monthly metric. Now I want to calculate the annual average of this CAPA Aging. 

Is there any way I can do an annual average on this CAPA Aging?

CAPA Aging = CALCULATE(AVERAGE('CAPA Aging'[Aging]),'CAPA Aging'[CAPA OWNING BUSINESS TYPE NAME] = "Trauma and Extremities" && 'CAPA Aging'[CAPA PRIORITY] <> "3"

 && 'CAPA Aging'[CAPA Still Open And Unimplemented On Report Month] = "Yes", DATESINPERIOD('Date Table'[Date], LASTDATE('Date Table'[Date]), -1, MONTH))

Thank you

[NanDeb]

@Anonymous  Do you have a MonthEnding column in your date table? If so I would use that and a virtual table to first calculate the Average Monthly CAPA Aging per year (if that is what you are trying to do? )

Montly Average CAPA =
VAR TotalAging =
    CALCULATE (
        SUM ( 'CAPA Aging'[Aging] ),
        'CAPA Aging'[CAPA OWNING BUSINESS TYPE NAME] = "Trauma and Extremities"
            && 'CAPA Aging'[CAPA PRIORITY] <> "3"
            && 'CAPA Aging'[CAPA Still Open And Unimplemented On Report Month] = "Yes"
    )
VAR Result =
    AVERAGEX ( SUMMARIZE ( 'Date Table', 'Date Table'[MonthEnding] ), TotalAging )
RETURN
    Result

 
Incidentally, I would probably do something similar for your Monthly Daily average:

Dailt Average CAPA =
VAR TotalAging =
    CALCULATE (
        SUM ( 'CAPA Aging'[Aging] ),
        'CAPA Aging'[CAPA OWNING BUSINESS TYPE NAME] = "Trauma and Extremities"
            && 'CAPA Aging'[CAPA PRIORITY] <> "3"
            && 'CAPA Aging'[CAPA Still Open And Unimplemented On Report Month] = "Yes"
    )
VAR Result =
    AVERAGEX ( SUMMARIZE ( 'Date Table', 'Date Table'[Date] ), TotalAging )
RETURN
    Result

I think calcualting averages like this makes them more robust when using in multiple different visualisations

[NanDeb]

I would use a virtual table and AVERAGEX. 

If you have a year column in your date tabl

Annual average Monthly aging = 

AVERAGEX(

         SUMMARIZE (Date, Date[Year]), 

         [CAPA Aging]

        )

This will return the monthly average for the year. Apologies if I misunderstood the requirements 

[Anonymous]

Hi @NanDeb ,

I've tried your code, however, it returns me the Dec avg data for every year. Do you know why?

I used Month Ending to calculate CAPA Aging. I don't know if this cause the different. 

[Anonymous]

The 1st screenshot is my current visual, I want to build a visual like the 2nd one. Is that possible?