Issue with Including Zero Counts in Average Calculation Measure in PowerBI

[SemaD_]

Hello,

I’m encountering an issue with a measure in PowerBI where I need to include zero counts in an average calculation. My current setup involves calculating the average number of sessions per CP ID, but I’m finding that the measure incorrectly excludes zero counts when a CP ID has no sessions on a particular date. (My data is regarding EV charging sessions)

Here is my current set-up:
1. A measure to work out the average number of sessions per charger:

SDR ID average per CP ID =

AVERAGEX(

    KEEPFILTERS(VALUES('Pogo E Connect Data'[CP ID])),

    CALCULATE(COUNTA('Pogo E Connect Data'[SDR ID])))

2. A measure to work out the average number of session dynamically using a customer Calendar table:

Average Number of Sessions.1 =

AVERAGEX(

    SUMMARIZE(

        'Calendar',

        'Calendar'[Year],

        'Calendar'[Quarter],

        'Calendar'[Month],

        'Calendar'[Date],

        "Average Sessions",

        CALCULATE(

            AVERAGEX(

                VALUES('Calendar'[Date]),

                COALESCE([SDR ID average per CP ID], 0)

            )

        )

    ),

    [Average Sessions]

)

My ultimate goal is to work out the Average number of sessions per charger per day. However some chargers do not have sessions everyday, which I tried to handle with COALESCE([SDR ID average per CP ID], 0), but frankly it just doesnt care and disregards the days without sessions. 

For example, on 18th June:

• Charger 1 = 3 sessions
• Charger 2 = 4 sessions
• Charger 3 = 0 sessions

The measures are returning an average of 3.5 sessions, while the correct average should be 2.3.

Does anyone have any ideas on how I could resolve this issue? I need to ensure that days with zero sessions are included in the average calculation.

Any help or suggestions would be greatly appreciated!

[bhanu_gautam]

@SemaD_ , Try using 

Total Sessions per CP ID per Day =
CALCULATE(
COUNTA('Pogo E Connect Data'[SDR ID]),
KEEPFILTERS(VALUES('Pogo E Connect Data'[CP ID])),
KEEPFILTERS(VALUES('Calendar'[Date]))
)

Average Sessions per CP ID per Day =

AVERAGEX(

    ADDCOLUMNS(

        VALUES('Calendar'[Date]),

        "Sessions", COALESCE([Total Sessions per CP ID per Day], 0)

    ),

    [Sessions]

)

One more to calculate the overall average number of sessions per day

Overall Average Sessions per Day =

AVERAGEX(

    SUMMARIZE(

        'Calendar',

        'Calendar'[Date],

        "Daily Average", [Average Sessions per CP ID per Day]

    ),

    [Daily Average]

)

[SemaD_]

Okay so this helped in some way by solving the 0 issue, so thank you! However I think because I am now focusing on the total sessions per day, rather than the average sessions per day, the overall average calculations don't seem quite correct. 
Rather than the average of the sum of sessions per day, I actually need the average of the average sessions per day. 
Take my example above, while the sum of sessions would be 7, the average of 7 sessions by 3 chargers is 2.33. For arguments sake lets say those chargers has the same output for the 30 days of June, then the monthly average would of course be 2.33. But based on the sum of sessions the monthly average would be 7. It is really important for me to do this on a per charger level. 
The inital formula is almost working that out, but excludes 0s. I think if I can resolve that then the overall average formula would make sense. 

SDR ID average per CP ID =

AVERAGEX(

    VALUES('Pogo E Connect Data'[CP ID]),

    CALCULATE(COUNTA('Pogo E Connect Data'[SDR ID])))

[SachinNandanwar]

I see empty values in the column. So just a note... AverageX ignores blank values but considers 0's in the calculation