Forum Discussion

Steels_Cat's avatar
Steels_Cat
Helper I
1 year ago
Solved

Count the highest value in table

Hello, I’m looking for a solution to find the highest value for each cycle and then sum them up after identifying the highest numbers. This is an example of what I’m looking for. I`m not sure how to create "Measure" column 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Steels_Cat 

     

    Thank you very much mh2587 for your prompt reply.

     

    Pls try this:

     

    Create measures.

     

    First query the maximum value in each group [Project], marked as 1.

     

     

    MaxValues = 
IF(
    SELECTEDVALUE('Table'[Value]) = 
    CALCULATE(
        MAX('Table'[Value]),
        FILTER(
            ALL('Table'),
            'Table'[Project] = MAX('Table'[Project])
        )
    ),
    1,
    BLANK()
)

     

     

     

    Counts the values that are 1 in each group [Cycle].

     

     

    Count Cycle = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[Cycle] = MAX('Table'[Cycle])
        &&
        'Table'[MaxValues] = 1
    )
)

     

     

    Here is the result.

     

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

5 Replies

  • mh2587's avatar
    mh2587
    Super User
    1 year ago
    Measure Sum = //Try this
SUMX(
    SUMMARIZE(
        Data, 
        Data[Cycle],
        "MaxValue", MAX(Data[Value])
    ),
    IF(
        Data[Cycle] IN {"Cycle 1","Cycle 2", "Cycle 3"},
        1,
        0
    )
)
  • Steels_Cat's avatar
    Steels_Cat
    Helper I
    1 year ago

    This is what I got as result. Only it looks for Values in and highlighted them as 1

    IN {"Cycle 1","Cycle 2", "Cycle 3"},

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Steels_Cat 

     

    Thank you very much mh2587 for your prompt reply.

     

    Pls try this:

     

    Create measures.

     

    First query the maximum value in each group [Project], marked as 1.

     

     

    MaxValues = 
IF(
    SELECTEDVALUE('Table'[Value]) = 
    CALCULATE(
        MAX('Table'[Value]),
        FILTER(
            ALL('Table'),
            'Table'[Project] = MAX('Table'[Project])
        )
    ),
    1,
    BLANK()
)

     

     

     

    Counts the values that are 1 in each group [Cycle].

     

     

    Count Cycle = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[Cycle] = MAX('Table'[Cycle])
        &&
        'Table'[MaxValues] = 1
    )
)

     

     

    Here is the result.

     

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Steels_Cat's avatar
      Steels_Cat
      Helper I
      1 year ago

      This it works but I need to use insted of 'Table'[Value], I need to use a measure which is Sumx('Table, Table[Value]). Is it possible to do that, beocuse in current logic does not work proparly