Forum Discussion

AmazingRandom's avatar
AmazingRandom
Icon for Helper II rankHelper II
2 years ago
Solved

How to summarize a grouped date with the highest value

This is a very new experience to me and after a few attempts I am out of ideas on how to achieve this.

 

Essentially, I need to show the highest condition for the day, however our data is being captured hourly, so I need to group the timestamps by date. That first part I managed to do easily. The second bit is where I'm stuck. 

 

Above is a screenshot of a scenario. Ignore the Manual and Current non compliant columns as the condition column already calculates the final values I need to summarise. So for this instance, the whole day should be flagged as 1, how can I make a new column that will look at the highest condition valueof the day and return it, this means the new column should only have the value 1 across all rows. Thanks in advance

Need Help
  • samratpbi's avatar
    samratpbi
    2 years ago

    Hi, considering that you would need a column, I created this sample dataset and created a conditional column:

    Below is the column calculation:

    MaxCond =
    CALCULATE(
        MAX(DateCond[Condition]),
        ALLEXCEPT(
            DateCond,
            DateCond[orderdate]
        )
    )
    As you can see 05/03/2024 has max condition 1, 06/03 has 0 and 07/03 has 2
     
    If this resolves your problem, then please accept this as solution to help others. Thanks!

4 Replies

  • samratpbi's avatar
    samratpbi
    Icon for Super User rankSuper User
    2 years ago

    Hi, considering that you would need a column, I created this sample dataset and created a conditional column:

    Below is the column calculation:

    MaxCond =
    CALCULATE(
        MAX(DateCond[Condition]),
        ALLEXCEPT(
            DateCond,
            DateCond[orderdate]
        )
    )
    As you can see 05/03/2024 has max condition 1, 06/03 has 0 and 07/03 has 2
     
    If this resolves your problem, then please accept this as solution to help others. Thanks!
    • AmazingRandom's avatar
      AmazingRandom
      Icon for Helper II rankHelper II
      2 years ago

      It worked, I had to edit the formula to also filter by location. I got a follow up question, how can I aggregate that into one single value for count? Off your example, there would be a count for each condition. Though it counts only one instance. So there would be 

       

      0 = 1 day

      1 = 1 day

      2 = 1 day

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Write this calculated column formula

    Highest condition value = calculate(max(Data[Condition]),filter(Data,Data[Date]=earlier(Data[Date])))

    Hope this helps.

    • AmazingRandom's avatar
      AmazingRandom
      Icon for Helper II rankHelper II
      2 years ago

      It didn't work, I'm getting the highest value possible in all rows, which is 2