Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I am struggiling to figure out why the Average is not calcucated properly:
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |