Forum Discussion
Take Max Value in Group Calculation if Multiple Values
Measure = var a = summarize('Table',[device],[timestamp],"mx",max([Value])) var b = SUMMARIZE(a,[timestamp],"my",var d=[timestamp] return sumx(filter(a,[timestamp]=d),[mx])) return maxx(b,[my])
| device | timestamp | Value |
| a | 7/31/2020 5:10:00PM | 15 |
| b | 7/31/2020 5:10:00PM | 9 |
| a | 7/31/2020 5:10:00PM | 13 |
| b | 7/31/2020 5:10:00PM | 20 |
| a | 7/31/2020 5:12:00PM | 10 |
| b | 7/31/2020 5:12:00PM | 14 |
| a | 7/31/2020 5:14:00PM | 25 |
| b | 7/31/2020 5:14:00PM | 7 |
| a | 7/31/2020 5:14:00PM | 17 |
Here is some example data. I want the max of the timestamp-grouped sum of the max reading of each device at a timestamp. (sounds complicated, I know).
Here is how it would be calculated with the above data.
1. Look at device/timestamp combinations that are repeated (a/5:10, b/5:10,a/5:14). Choose the max value for those and throw out the other one. (We do not need a = 13, b = 9, a = 17).
2. For each timestamp, take the sum of the readings for that timestamp (excluding the ones we threw out).
- 5:10 - 15+20 = 35
- 5:12 - 10 + 14 = 24
- 5:14 - 25 + 7 = 32
3. Out of these values, take the max. So the answer would be 35.
The formulas I have given in my original post give me this workflow, except for the taking the max value for each device at each timestamp if there are multiple.
Thank you for your help!
Measure =
var a = summarize('Table',[device],[timestamp],"mx",max([Value]))
var b = SUMMARIZE(a,[timestamp],"my",var d=[timestamp] return sumx(filter(a,[timestamp]=d),[mx]))
return maxx(b,[my])