Average auto-Calculation

[vika160]

Hi All,

I am struggiling to figure out why the Average is not calcucated properly:

_Avg Days Open-Complaints = Calculate(AVERAGE(COMPLAINTS[_Days Open]), NOT ISBLANK(COMPLAINTS[_Days Open]))

if I just calculate totals and divide them I an getting the different result.

on left side is Average function, on the right is "manual" calculation.

Can someone help/direct/explain this?

Thank you very much!

[vika160]

Hi Pat,

Thank you very much for so detailed explanations.

I had a suspicion that standard AVE function was affected by Blank duration for Complaints with the same Start and End dates. it looks it was correct thought.

I really appreciated your hints on rewrinting the formulas.

Thank you very much.

Victoria

[mahoneypat · Accepted solution]

The main reason for the difference is that your column for days open returns blank if the complaint is closed on the same day it's opened.  If you remove the = from <= in that expression, the results get much closer.  The small difference after that is because you have one complaint with a closed date < open date.  Since that one included in the complaint count but not the days open total, the result is incorrect.  I believe the correct result is 147.06.  

A couple things:

You don't need the CALCULATE around your _TotalDaysOpen and _TotalComplaints measures.

I would rewrite your column like this (to be more efficient).

Days Open Column =
VAR vCloseDate =
    IF (
        ISBLANK ( COMPLAINTS[Closed_Date] ),
        TODAY (),
        COMPLAINTS[Closed_Date]
    )
VAR vDaysOpen =
    DATEDIFF (
        COMPLAINTS[Opened_Date],
        vCloseDate,
        DAY
    )
RETURN
    IF (
        vDaysOpen >= 0,
        vDaysOpen,
        BLANK ()
    )

You also don't need the column and can do it all in a measure.  However, if you plan to use that column in a visual axis or legend, you need it.

New Avg Open =
VAR vSummary =
    ADDCOLUMNS (
        SUMMARIZE (
            COMPLAINTS,
            COMPLAINTS[ID],
            COMPLAINTS[Opened_Date],
            COMPLAINTS[Closed_Date]
        ),
        "cDaysOpen",
            DATEDIFF (
                COMPLAINTS[Opened_Date],
                IF (
                    ISBLANK ( COMPLAINTS[Closed_Date] ),
                    TODAY (),
                    COMPLAINTS[Closed_Date]
                ),
                DAY
            )
    )
RETURN
    AVERAGEX (
        FILTER (
            vSummary,
            [cDaysOpen] >= 0
        ),
        [cDaysOpen]
    )

Regards,

Pat

[amitchandak]

@vika160 , formula seems fine. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

with expected values

[vika160]

here is the file:

https://1drv.ms/u/s!Ai54qWrC0lclg8YAPZbzxCa3vdOZTw?e=qYxAOM