PowerBI DAX: average per time interval

130 Views Asked by At

I have the following table in PowerBI existing of a column with time stamps (data type: Time, format hh:mm:ss) and the counted amount of sold goods (whole numbers). Example table:

Time Stamp Sold Goods

18:25:50 1

07:21:05 3

11:45:35 2

.... ...

I now want to create a DAX query which calculates the average amount of sold goods per 30 min interval.

Output would then be:

Time interval Average amount of sold goods

07:00 - 07:30 5

07:30 - 08:00 3

08:00 - 08:30 2

... ....

I tried with the following DAX code which should solve the problem: First determine time interval and create new column out of it:

Time Interval = TIME(HOUR('MyTable'[TimeStamp]), FLOOR(MINUTE('MyTable'[TimeStamp]), 30), 0)

Second: build average

Average Sold Goods = 
   ADDCOLUMNS(
       SUMMARIZE('MyTable', 'Table'[Time Interval]),
       "Average",
       AVERAGE('MyTable'[Sold Goods])
   )

First code works perfect, it showes the time interval in hh:mm:ss. Example: 18:25:50 -> 18:00:00

Issue comes with the second Dax code: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

Thank you very much.

0

There are 0 best solutions below