cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nmck86 Member
Member

Simple AVG function

I am using a simple average calculation and it seems when a month there are no values it is excluding that month from the avg all together... is there a way to use the average function and it take every single month into account and divide by that number even if that month is blank and has no values?

 

AVERAGEX(SUMMARIZE(VALUES('Calendar'[Month]),[Month],"ABCD",[CALC_FIELD]),[ABCD])
4 REPLIES 4
OwenAuger Super Contributor
Super Contributor

Re: Simple AVG function

@nmck86

One way to ensure that blanks are treated as zeros for the purpose of the average calculation is to add zero to the expression being averaged.

 

At the same time, I would simplify your formula slightly to something like this:

 

=
AVERAGEX (
    VALUES ( 'Calendar'[Month] ),
    [CALC_FIELD] + 0
)

Regards,
Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Simple AVG function

Here's how I tackled this:

 

1. Add an index column to your table, usually a Calendar Table ( super easy with power query)

Power Query Table.png

Create simple measure for Total Sales:

Total Sales = sum ( 'Avg Dont Skip'[Sales] )

Create a cummulative Sales and Cumulateive Rows measures

Cumu Sales =
CALCULATE (
    [Total Sales],
    FILTER (
        ALL ( 'Avg Dont Skip' ),
        SUM ( 'Avg Dont Skip'[Index] ) >= 'Avg Dont Skip'[Index]
    )
)

Cumu Rows =
CALCULATE (
    COUNTROWS (
        FILTER (
            ALL ( 'Avg Dont Skip' ),
            'Avg Dont Skip'[Index] <= SUM ( 'Avg Dont Skip'[Index] )
        )
    )
)

so we have our numerator and denominator, then just divide from there:

Avg Sales = DIVIDE([Cumu Sales] , [Cumu Rows] )

Then the final table:

Final Table.png

nmck86 Member
Member

Re: Simple AVG function

Here is a file you can look at. The structure is slightly different. https://drive.google.com/open?id=1siXptcM3FGuvQ3VH4qbC3aBdgFUa0Dgl Can you take a look at this and see if you can get the category breakdown to actually show as an average?
nmck86 Member
Member

Re: Simple AVG function

Also, the easiest way to see this is to add a month filter and you will notice that not in all months will we have values... however, I still want the average to be over the 10 month timespan even if we do not have results so in the sample below the average would be 19.07.

 

 

2018-12-10_13-46-42 sample.png

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)