cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
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!
 
PowerBI Average.PNG

 

 

 

1 ACCEPTED SOLUTION
Super User IV
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 =
    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





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

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
Helper II
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
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 =
    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





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

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Super User IV
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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors