Forum Discussion

bobam's avatar
bobam
Frequent Visitor
5 years ago
Solved

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 mont...
Need Help
  • mahoneypat's avatar
    mahoneypat
    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

     

     

mahoneypat's avatar
mahoneypat
Microsoft Employee
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

 

 

bobam's avatar
bobam
Frequent Visitor
5 years ago

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