Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to calculate average based on a calculated measure

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
4 REPLIES 4
NanDeb
Helper I
Helper I

@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
Helper I
Helper I

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
Not applicable

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
Not applicable

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

Lucasw_0-1670010194347.png

Lucasw_1-1670010241243.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.