Forum Discussion
Min and Max Columns summarising multiple rows
Hi all,
I have a large dataset of half hourly temperatures for two decades. I have created a column which summarises the same days across the multiple years, from 1 to 366 ("Day" column). I need to now calculate the minimum temperature and maximum temperature for each Day (1 to 366).
I tried a simple DAX Calculate Min filtering by Day but it has not summarised as I wish it to. Further when I did the same for DAX Calculate Max filtering by Day, a circular dependency error occurred.
A table in the report view does this perfectly, however I need to calculate the average between the min and max value for each Day, which I understand has to be computed based on Columns.
Thank you
Hi Anonymous,
If I've correctly understood your task, you could solve it with the help of the three measures below and a table visual:
MinTemp = MIN ( [Temperature] ) MaxTemp = MAX ( [Temperature] ) AvgTemp = ( [MaxTemp] + [MinTemp] ) / 2In the test case below you can see dates, but it will work with days too:
Best Regards,
Alexander
2 Replies
Hi Anonymous,
If I've correctly understood your task, you could solve it with the help of the three measures below and a table visual:
MinTemp = MIN ( [Temperature] ) MaxTemp = MAX ( [Temperature] ) AvgTemp = ( [MaxTemp] + [MinTemp] ) / 2In the test case below you can see dates, but it will work with days too:
Best Regards,
Alexander
HI Anonymous,
You can try to use following calculate columns formula to get the max and min temperature based on current date:
daily MinT = CALCULATE ( MIN ( Table1[Temperature] ), FILTER ( Table1, Table1[Date] = EARLIER ( Table1[Date] ) ) ) daily MaxT = CALCULATE ( MAX ( Table1[Temperature] ), FILTER ( Table1, Table1[Date] = EARLIER ( Table1[Date] ) ) )Regards,
Xiaoxin Sheng