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])
Hi emmawdmi22 ,
In order to better understand your demands and give the right solution, could you please provide some more specific information? such as your desensitized example data and a screenshot of your desired results?
Thanks for your efforts & time in advance.
Best regards,
Community Support Team_Binbin Yu
| 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])