cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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             
month123456789101112avg_total_yr
calculation test1100,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             
month123456789   avg_total_yr
calculation test1100,00%100,00%100,00%99,84%98,80%99,36%99,25%99,57%99,73%   99,62%

 

tnx !

B

 

 

1 ACCEPTED SOLUTION
Super User IV
Super User IV

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

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
Super User IV
Super User IV

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

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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%.

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.

Capture1.PNG

 

Final result must show average from 1-12, but show only months with data and calculate yearly avarage.

Capture2.PNG

I have test pbix file, cant share on onedrive because of company permissions...i can send you by email.

tnx...B

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors