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.