Count the highest value in table

[Steels_Cat]

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 

[Anonymous · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Anonymous · Accepted solution]

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]

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 

[Steels_Cat]

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"},

[mh2587]

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