How to find average for multiple data in a day?

[syazwan95]

Greetings all,

Please refer the image that I've attached. I'm extracting data from my company's database, and it works such that it will record multiple readings from the source on the same day. I'm trying to find a way such that I can average these data and only show one value for that day (averaged value).

In the image, you can see that I have multiple data for 31/3/2019. I just want to find its average on that day and only show one value. I would like to do the same for other dates as well.

Anyone knows how I can do this through DAX/calculated column/Measure?

[TeigeGao · Accepted solution]

Hi @syazwan95 ,

Actually, neither measure nor calculated column can only show one value for that day directly, for calculated column, it will display the average data for each day on each row.

In this scenario, we can use the Matrix visual to display it.

First we can create a calculated column using the following DAX query:

Column = CALCULATE(AVERAGE(Table1[IP_trend_Value]),FILTER(ALL(Table1),Table1[IP_TREND_time] = EARLIER(Table1[IP_TREND_time])))

Then refer to this blog to display it: https://data-flair.training/blogs/power-bi-matrix-visual/

Best Regards,

Teige