Averagex with a calculate filter

[crisher]

I am trying to calculate the average of the Billing Total MTD, but filter out weekends or Holidays. In the below, 0 indicates that the date is neither a weekend nor a holiday. I feel like I am missing something completely basic.

MTD Daily Average = CALCULATE(

AVERAGEX (

DATESMTD ( 'Calendar'[Billing Date] ),

[Billing Total] ),

'Calendar'[WeekendOrHoliday]=0

)

[v-kkf-msft]

Hi @crisher ,

If you only calculate the average for rows with data, then try the formula of @DataInsights . 

If you want to calculate the average for all weekdays in current month, try the following formula.

Measure = 
AVERAGEX ( 
    ADDCOLUMNS (
        CALCULATETABLE (
            'Calendar', 
            FILTER (
                'Calendar',
                'Calendar'[WeekendOrHoliday] = 0 
                    && FORMAT ( 'Calendar'[Billing Date], "YYYYMM" ) = FORMAT ( TODAY ( ), "YYYYMM" ) 
            )
        ),
        "BillingTotal", COALESCE ( [Billing Total], 0 )
    ),
    [BillingTotal]
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataInsights]

@crisher,

Try this measure:

MTD Daily Average =
CALCULATE (
    AVERAGEX ( 'Calendar', [Billing Total] ),
    DATESMTD ( 'Calendar'[Billing Date] ),
    'Calendar'[WeekendOrHoliday] = 0
)

[crisher]

Hey @DataInsights, I think this is close. But, I do need the billings that are occurring over the weekend as part of Mondays' average calculation. Does that make sense?

[crisher]

Actually, I don't think this is right. At the moment, it is simply doing the average of that day. The intent is to do a running average for the month and remove holidays and weekends. Right now, it is just doing the average of that single day. See below...

[DataInsights]

@crisher,

As I understand it, you want to include all billings (numerator), but exclude weekends and holidays from the count of days (denominator). Revised measure:

MTD Daily Average = 
// include weekends and holidays
VAR vBillings =
    CALCULATE ( [Billing Total], DATESMTD ( 'Calendar'[Billing Date] ) )
// exclude weekends and holidays
VAR vDays =
    CALCULATE (
        COUNTROWS ( 'Calendar' ),
        DATESMTD ( 'Calendar'[Billing Date] ),
        'Calendar'[WeekendOrHoliday] = 0
    )
VAR vResult =
    DIVIDE ( vBillings, vDays )
RETURN
    vResult