2 Replies Latest reply: Mar 20, 2018 7:08 AM by Sophie Webster RSS

    How to count the maximum value for each distinct ID?

    Sophie Webster

      I am trying to get a bar chart to show the number of times each maximum value occurs, per distinct ID. The maximum value can be an integer between 1 and 6. An example of data is below:

       

      Episode IDMost recent form ID
      1013
      1012
      1011
      2502
      2501
      552
      551
      6384
      6383
      6382
      6381

       

      I am wanting to show a count of the maximum number in the 'most recent form ID' for each distinct episode ID. So, for example, the maximum value of the form ID for episode 101 is 3, so this counts as 1 episode ID. 2 is the maximum number for IDs 55 and 250, so this should count 2 distinct episode IDs. However, my current formula is currently counting every distinct episode ID that has a 2 (even if it is not the max value), so in this example it is returning a count of 4 (not 2).

       

      My current formula is this:

       

      Max(Aggr(Count({<[Opened in past 12 months?] = {'Yes'}>} distinct [Caf Episode ID]), [Most recent Form ID]))


      And this produces the following graph:

      2dd8645025a443f188b690840c3cb2b7.jpg

       

      I know this is wrong, because when I drill down into each column, the single episode ID that falls under '6' also appears in every other 'most recent form ID' group - but I only want it to appear in '6', because this is the maximum 'most recent form ID' for this distinct Episode ID...

       

      Can anyone help? Thank you in advance!