How to calculate month averages when there are days without values

[lukasss]

Hello comunity,

I've searched my answer online but couldn't find a sollution.

My goal is to calculate average page views per month including all days in a given month.

The Avg below shows 1.5 but it doesn't ake into consideration days without any page views. However,  I need 12/31(days) = 0.4.

I have a dimension date table linking my page views table. How do I divide page views by all days in a gien month? 

[v-yanjiang-msft · Accepted solution]

Hi @lukasss ,

According to your description, here's my solution. Create a measure.

Average =
VAR _SUM =
    SUMX (
        FILTER (
            ALL ( 'Page views' ),
            MONTH ( 'Page views'[Date] ) = MONTH ( MAX ( 'Page views'[Date] ) )
        ),
        'Page views'[PageViews]
    )
VAR _DAYS =
    COUNTROWS (
        FILTER (
            ALL ( 'Date' ),
            MONTH ( 'Date'[Date] ) = MONTH ( MAX ( 'Page views'[Date] ) )
        )
    )
RETURN
    DIVIDE ( _SUM, _DAYS )

Get the correct result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.