Wow. This was much more challenging than I thought.

I just learned the following from Greg Deckler here:

- DAX & Excel have no true time duration data type!

- That's why performing aggregate functions on HH:MM:SS data does NOT work.

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

So after performing the first 3 steps in the post above, Step 4 has the below sub-steps. You can probably combine some of my steps, but I left them all in to show the thought process.

4A) Manually calculate the average weighted time in Excel, which is:

- 4,732 seconds

- OR 01:18:52

These are the answers to which we will compare our DAX calculations at the end.

NOTE: Remember that Row 7 counts twice @ 1:19:00, which makes a total row count of 15.

4B) Create a Calculated Column in the 'Rows To Keep' table for [Seconds].

4C) Create a Calculated Column in the 'Rows To Keep' table for [Seconds Weighted].

4D) Create a measure to calculate [Avg Time in SECONDS].

NOTE: As you will see in the results screenshot below, this measure is a perfect match for our manually calculated number of seconds, 4732.

4E) Create a measure to convert from [Avg Time in Seconds] back into [Avg Time in HH:MM:SS].

NOTE: As you will see in the results screenshot below, the final DAX calculation is not 01:18:52, but rather 01:18:01. I believe this has to do with the differences between:

- DIVIDE function

- MOD function

- / operator

I messed with this final formula for a while, trying variations of the above 3 options, but the closest I could get was 20 seconds from 01:18:52. Hoping you can get this last formula to your satisfaction of seconds.

Unfortunately, since DAX & Excel do not support a true duration data type, we have to workaround this by converting to seconds, performing the calculation, & then converting back again.

Hope this is helpful to you.

Regards,

Nathan

Forum Discussion

Filtering Data

SQL ALTER command

DAX command

Filter data using slicer

If command