cancel
Showing results for
Did you mean:
Helper II

## Average auto-Calculation

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!

1 ACCEPTED SOLUTION
Super User IV

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 =
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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

4 REPLIES 4
Helper II

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

Super User IV

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 =
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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Super User IV

@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

Proud to be a Super User!

Helper II

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!