Forum Discussion
Yearly average
- 5 years ago
You can do this without creating additional tables, doing it all in your measure with a virtual table.
Assuming you have a Date table with a YearMonth column for all months (including those w/o data yet), something like this should work. This assumes you will make a visual that does not include the YearMonth column but has a filter on it limiting it to one year. The first IF() puts a 100 in your calculation if your measure is blank for that YearMonth. The second IF() returns a blank if Your Measure is blank for that YearMonth (i.e., not show Oct-Dec in your example).
Year Avg =
VAR summary =
ADDCOLUMNS (
DISTINCT ( Date[YearMonth] ),
"@result",
VAR result = [Your Measure]
RETURN
IF (
ISBLANK ( result ),
100,
result
)
)
RETURN
IF (
ISBLANK ( [Your Measure] ),
BLANK (),
AVERAGEX (
summary,
[@result]
)
)Regards,
Pat
You can do this without creating additional tables, doing it all in your measure with a virtual table.
Assuming you have a Date table with a YearMonth column for all months (including those w/o data yet), something like this should work. This assumes you will make a visual that does not include the YearMonth column but has a filter on it limiting it to one year. The first IF() puts a 100 in your calculation if your measure is blank for that YearMonth. The second IF() returns a blank if Your Measure is blank for that YearMonth (i.e., not show Oct-Dec in your example).
Year Avg =
VAR summary =
ADDCOLUMNS (
DISTINCT ( Date[YearMonth] ),
"@result",
VAR result = [Your Measure]
RETURN
IF (
ISBLANK ( result ),
100,
result
)
)
RETURN
IF (
ISBLANK ( [Your Measure] ),
BLANK (),
AVERAGEX (
summary,
[@result]
)
)
Regards,
Pat
Hi mahoneypat,
its working like a charm !!! Nice work.
I just edit the RETURN function. Instead of 100 changed to 1, because I alredy have format in percentage.
RETURN
IF (
ISBLANK ( result ),
1,
result
ps. created custom table with distinct on yearmonth and noticed that in "@result" i have in return 10000%.