DAX average including zeroes

Question

My question is that I'd like to calculate a daily average taking into account days with zeroes.

Let me clarify it:

I'd like to calculate the average daily value of units for each category, with the following table:

When I sum up the values for each day and category, I get:

I'd like to include in the average calculation the zeroes.

I use the following code:

SUMMARIZE(
    Data,
    Data[Category],
    "Average",
    AVERAGEX(
        SUMMARIZE(
            Data,
            Data[Date],
            "Sum of Units",
            SUM(Data[Units])
        ),
        [Sum of Units]
    )
)

But the problem is that for category B it doesn't take into account those days with 0s.

Could you please guide me how to solve it?

Thanks in advance!

Jorge

Answer 1

I think the best way is to add the missing zeroes, using UNION function:

SUMMARIZE(
    Data,
    Data[Category],
    "Average",
    AVERAGEX(
        UNION(
            SUMMARIZE(
                Data,
                Data[Date],
                "Sum of Units",
                SUM(Data[Units])
            ),
            ADDCOLUMNS(
                EXCEPT(
                    ALL(Data[Date]),
                    VALUES(Data[Date])
                ),
                "Sum of Units",
                0
            )
        ),
        [Sum of Units]
    )
)