Take Max Value in Group Calculation if Multiple Values

[emmawdmi22]

In my report, I currently have a working measure called *Max Val (see below).  I have several devices that report at every timestamp. The calculation takes the sum of values for the devices at each timestamp, and then finds the max of these sums over a time period. 

My issue is that sometimes the devices report multiple times at one timestamp, and I only want to use one reading per device per timestamp. How can I make it so, if a device has multiple readings at a timestamp, I only take the max value for that specific device to add to the sum? 

Current calculations

*Max Val = MAXX(VALUES(Table[timestamp]), [SumValue])

SumValue = CALCULATE(SUM(Table[Value]))

and deviceId variable denotes which device

timestamp gives date in format like 7/31/2020 5:10:00PM

[lbendlin]

I only want to use one reading per device per timestamp.

yes, but which one?

You can do a SUMMARIZE over the deviceID, add a MAXX (value) column for each deviceid, and then do the MAXX over the result.

[Anonymous]

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

[emmawdmi22]

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!

[lbendlin · Accepted solution]

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])