Dax: How to get Average of Averages

[Khpuryear]

Hey There 👋

I have a Measure to pull the dwell time for each row as seen below. Noting the 2nd and 3rd rows from the bottom should technically be 00:00. I'm also not counting rows if there are no actual times in the ActTimeFrom/ActTimeTo columns. 

I also have a visual that pulls the average dwell time per entity as seen in bar chart below. 

The averages in the bar chart are good. My challange is I need a card visual for the "average of the averages," which would equate to the average across all entities (i.e., drivers). What's odd is if I create a card visual and filter by Kyle only, the average is correct (00:33). If I remove the filter, the average is incorrect.

Looking at the bar chart I provided, the card visual for average across all drivers should be: 

(02:15 + 01:16 + 01:00 + 00:33 + 00:15 + 00:02 + 00:02 + 00:00)/8 = 0:40

The table name is vw_Loads and there are two Measures at play.

The Measure for the "Dwell Time" calculation in my table visual (First Screenshot) is: 

VAR vDuration = SUMX(vw_Loads, DATEDIFF(vw_Loads[ActTimeFrom], vw_Loads[ActTimeTo], SECOND))

VAR vHours = INT(vDuration / 3600)

VAR vMinutes = INT(MOD(vDuration, 3600) / 60)

RETURN

    IF(vDuration = 0, BLANK(), FORMAT(vHours, "00") & ":" & FORMAT(vMinutes, "00"))

The Measure for the "Dwell Time Averages" in my bar chart visual (Second Screenshot) is: 

VAR vAvgDuration =

    AVERAGEX(vw_Loads, DATEDIFF(vw_Loads[ActTimeFrom], vw_Loads[ActTimeTo], SECOND))

RETURN

    IF(vAvgDuration = 0, BLANK(), vAvgDuration / 3600)

Any info on how to create a Measure or Column for the average of the averages would be greatly appreciated.

Thanks 🍻

[v-pnaroju-msft]

Hi Khpuryear,

Thank you for the update and for sharing the insights to resolve the issue.

Should you have any further queries, please do not hesitate to reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi Khpuryear,

Thank you for your update.

We are pleased to learn that you have identified a solution to the issue. Kindly share the insights and the approach you employed to resolve the matter, so that it may benefit the wider community.

If you have any further queries, please do not hesitate to reach out to the Microsoft Fabric community.

Thank you.

[Khpuryear · Accepted solution]

I was able to use another Measure I already had and did not in fact need the average of the averages. I actually just needed the average of all records. Got mixed up spending, too much time on this project 😂Copied below to reference if need:

VAR vAvgDuration =

    AVERAGEX(vw_Loads, DATEDIFF(vw_Loads[ActTimeFrom], vw_Loads[ActTimeTo], SECOND))

VAR vHours = INT(vAvgDuration / 3600)

VAR vMinutes = INT(MOD(vAvgDuration, 3600) / 60)

RETURN

    IF(vAvgDuration = 0, BLANK(), FORMAT(vHours, "00") & ":" & FORMAT(vMinutes, "00"))

[Khpuryear]

I think I found another solution for the time being. Thank you everyone for you guidance 🙏

[v-pnaroju-msft]

Hi Khpuryear,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi Khpuryear,

We would like to follow up and see whether the details we shared have resolved your problem.
If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Thankyou, @jgeddes, for your response.

Hi Khpuryear,

We appreciate your question on the Microsoft Fabric Community Forum.

Based on my understanding of the issue, please find attached a screenshot and a sample PBIX file that may assist in resolving the matter:

We hope the information provided helps to resolve the issue.
Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[jgeddes]

Delete the comma after [Dwell Time_v2_...]

[Khpuryear]

I also added a card visual to see if it calculated correctly (00:40) based on my date slicer and corresponding bar chart, but it's pulling a different total as well.

[jgeddes]

It looks like there is an issue with the virtual table in the formula.
What does the output of the table expression look like when you evaluate it in DAX Query View?

Out of curiosity, is there any difference in the result if you use [Dwell Time_v2_Average] instead?

[Khpuryear]

Wasn't sure specifically what you needed, so I copied a few things below.

Regarding "Dwell Time_v2_Average", I have a few versions I've been troubleshooting with. That one doesn't work with your Dax measure (See Below).

The only Dax measures I'm using are "Dwell Time v2" for the dwell time, and "Dwell Time V2 Average for Visuals" for averaging the dwell time per driver in my bar chart. It's just the average of the averages (i.e., average for all drivers based on date slicer) that isn't calculating correctly.

I tried a Measure vs a Column, and that's also incorrect.

[jgeddes]

I was just hoping to the result of the following table expression.

Based on the result 0.43 versus the result of 0.40 that you are looking for, it means the table the AVERAGEX is iterating over needs to be amended. 

[Khpuryear]

Interesting...so it works, but the totals are not correct. The totals in the bar chart and the first column are correct, but the new column I created is different. It also appears to have ignored the date slicer and pulled my entire list of drivers vs just the ones who have activity in the date range.

[jgeddes]

You could consider this approach...

Average of Driver Averages =
var _vTable = 
SUMMARIZE(
vw_Loads[Driver],   //I am assuming the driver column is in the vw_Loads table
"_average", [Dwell Time Averages],
)
RETURN
AVERAGEX(
_vTable,
[_average]
)

You would be creating a virtual table of driver averages and then taking the average of those values.
Hope this gets you pointed in the right direction.

[Khpuryear]

I tried that, and get the error below. Although the table name and field name are correct, it doesn't recognize it. You can see the table and field (DriverName) in the dataset. It also can't find the name _average.

[jgeddes]

I neglected to include the first argument in the SUMMARIZE function.
If you add vw_loads as the first argument the errors should go away.

Average of Driver Averages =
var _vTable = 
SUMMARIZE(
vw_loads,
vw_Loads[Driver],   //I am assuming the driver column is in the vw_Loads table
"_average", [Dwell Time Averages],
)
RETURN
AVERAGEX(
_vTable,
[_average]
)

[Khpuryear]

Progress, but a new error now 💩