Forum Discussion

emmawdmi22's avatar
emmawdmi22
Icon for Helper I rankHelper I
3 years ago
Solved

Take Max Value in Group Calculation if Multiple Values

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
Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago
    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])

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      emmawdmi22
      Icon for Helper I rankHelper I
      3 years ago
      devicetimestampValue
      a7/31/2020 5:10:00PM15
      b7/31/2020 5:10:00PM9
      a7/31/2020 5:10:00PM13
      b7/31/2020 5:10:00PM20
      a7/31/2020 5:12:00PM10
      b7/31/2020 5:12:00PM14
      a7/31/2020 5:14:00PM25
      b7/31/2020 5:14:00PM7
      a7/31/2020 5:14:00PM17

       

      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's avatar
        lbendlin
        Icon for Super User rankSuper User
        3 years ago
        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])
  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

     

    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.