Forum Discussion
Yearly average
Hi,
few questions.
1. How to calculate yearly average with monthly values (as in table 1) where data months after 09 doesnt exit in data ?
- so in data table exists only values from 01-09 months
- for months that not exist value is 100% and this values must bi in calculation on yearly average
- visual must show only months from 1-9
Basicly I can insert in table data for missing months, but is there possibility to replace this with dax to create average_yearly %. In that case, i have additional task to create calculation on whole year but to show data only for months in filter page with average_yearly %. Or countrows for month, so if countrows is 9, add in calculation 3 months for average_yearly % with values 100%. (?)
| table1 | |||||||||||||
| month | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | avg_total_yr |
| calculation test1 | 100,00% | 100,00% | 100,00% | 99,84% | 98,80% | 99,36% | 99,25% | 99,57% | 99,73% | 100,00% | 100,00% | 100,00% | 99,71% |
| table2 | |||||||||||||
| month | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | avg_total_yr | |||
| calculation test1 | 100,00% | 100,00% | 100,00% | 99,84% | 98,80% | 99,36% | 99,25% | 99,57% | 99,73% | 99,62% |
tnx !
B
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
3 Replies
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,
I have, dim_date table but in visual I need to include month period (based on one year to select). Using your formula, average yearly doesn't give expected result. Second picture included measure for year avg.
Final result must show average from 1-12, but show only months with data and calculate yearly avarage.
I have test pbix file, cant share on onedrive because of company permissions...i can send you by email.
tnx...B
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,
resultps. created custom table with distinct on yearmonth and noticed that in "@result" i have in return 10000%.
