Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
@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
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
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.
The 1st screenshot is my current visual, I want to build a visual like the 2nd one. Is that possible?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |