Weighted Time Average from table of times.

[Anonymous]

I've got a calendar table of dates, and have created a SUMMARIZECOLUMNS measure that tabulates every 'event time' within that day, and the active quantity at that time. For example, for the 29th of January this table is generated:

Event Time	Active Quantity
29/01/2024 08:00	100
29/01/2024 09:00	125
29/01/2024 12:00	300
29/01/2024 15:00	300
29/01/2024 17:00	200
29/01/2024 22:00	25

I'm trying to calculate the Average Active Quantity, weighted by the amount of time between Events. To calculate this I need to generate an intermediate table, with each time period:

Start Time	End Time	Hours Difference	Active Quantity
29/01/2024 08:00	29/01/2024 09:00	1	100
29/01/2024 09:00	29/01/2024 12:00	3	125
29/01/2024 12:00	29/01/2024 15:00	3	300
29/01/2024 15:00	29/01/2024 17:00	2	300
29/01/2024 17:00	29/01/2024 22:00	5	200

How would I generate this table, and finally calculate the Average of 'Active Quantity' weighted by 'Hours Difference' in one measure?

[Anonymous · Accepted solution]

Hi @Anonymous ,

Hope everything is going well.

Based on your description I would create an intermediate table and then create measures to calculate the weighted average.

Based on the fields you provide, select New Table in the Model view.

The DAX syntax is:

Intermediate Table =
ADDCOLUMNS(
SUMMARIZECOLUMNS(
'Table'[Event Time],
'Table'[Active Quantity],
"End Time",
CALCULATE(
MIN('Table'[Event Time]),
FILTER(
ALL('Table'),
'Table'[Event Time] > MAX('Table'[Event Time])
)
)
),
"Hours Difference",
DATEDIFF(
'Table'[Event Time],
[End Time],
HOUR
)
)

After successful creation, the data is displayed as follows:

Next, create a measure to calculate the weighted average. The DAX syntax is as follows:

Weighted Average Active Quantity =
SUMX(
'Intermediate Table',
[Active Quantity] * [Hours Difference]
)
/ SUM('Intermediate Table'[Hours Difference])

After creation, the data can be displayed in the "Report" view. The "Table" visual object is used to display the intermediate table, and the "Card" visual object is used to display the weighted average. The effect is as follows:

Power BI Desktop add-on attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[123abc]

To achieve this in Power BI, you can follow these steps:

1. Generate the intermediate table with start time, end time, hours difference, and active quantity.
2. Calculate the weighted average using DAX measures.

Here's how you can generate the intermediate table in Power BI:

IntermediateTable =
VAR MaxIndex = COUNTROWS('YourTable') - 1
RETURN
ADDCOLUMNS (
GENERATESERIES(0, MaxIndex - 1, 1),
"Start Time", 'YourTable'[Event Time],
"End Time", 'YourTable'[Event Time] + TIME(1, 0, 0),
"Hours Difference",
IF(
[Index] = 0,
HOUR('YourTable'[Event Time] + TIME(1, 0, 0) - 'YourTable'[Event Time]),
HOUR('YourTable'[Event Time] + TIME(1, 0, 0) - 'YourTable'[Event Time + [Index]])
),
"Active Quantity", 'YourTable'[Active Quantity]
)

Explanation:

• MaxIndex finds the maximum index of the table.
• GENERATESERIES generates a series from 0 to MaxIndex - 1.
• ADDCOLUMNS creates new columns for the intermediate table.
• "Start Time" and "End Time" are the same as the original time but with an added hour.
• "Hours Difference" calculates the difference in hours between consecutive event times.
• "Active Quantity" brings the active quantity from your original table.

Now, to calculate the weighted average of 'Active Quantity' by 'Hours Difference', you can use the following measure:

WeightedAverage =
DIVIDE(
SUMX('IntermediateTable', 'IntermediateTable'[Active Quantity] * 'IntermediateTable'[Hours Difference]),
SUM('IntermediateTable'[Hours Difference])
)

This measure iterates over the rows of the intermediate table, multiplies the 'Active Quantity' by 'Hours Difference', sums up the results, and then divides it by the sum of 'Hours Difference'.

You can now use the WeightedAverage measure in your reports to get the desired result. Make sure to adjust column and table names according to your actual data model in Power BI.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.