Forum Discussion

robertosangi's avatar
robertosangi
Icon for Helper I rankHelper I
4 years ago
Solved

Highest value per cluster

Hi,    In my case I want to obtain the highest value (top 1) of a column divided per cluster. Here the example: Actually I filtered my column as per picture because is not possible to filt...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi robertosangi ,

     

    According to your screenshot, I think your table should look like as below.

    I think you want to show values in red box.

    I suggest you to create a rank measure and use it as a filter in visual level filter.

    RANK =
VAR _RANK =
    RANKX (
        ALLEXCEPT ( 'Table', 'Table'[Unite], 'Table'[Blue Team] ),
        CALCULATE ( SUM ( 'Table'[IGB_NoLoc] ) ),
        ,
        DESC,
        DENSE
    )
RETURN
    _RANK

    Result is as below.

    Best Regards,
    Rico Zhou

     

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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

robertosangi , Create a rank on the innermost column and filter on 10

 

rankx(allselected(Table[cluster]), [IGB],,desc,dense)

robertosangi's avatar
robertosangi
Icon for Helper I rankHelper I
4 years ago

Hi amitchandak , i don't want to filter on 10 but on the maximum value. It is possible?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi robertosangi ,

     

    Do you want to filter your matrix visual by two filters, 1. values in IGB_Noloc are greater than 10, 2. maxest value in  IGB_Noloc in per cluster ?

    Here I suggest you to create a measure to filter your visual.

    My sample:

    Measure:

    IGB_NoLoc Greater than 10 and Top 1 =
VAR _IGB_NoLoc =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Type] = "IGB_NoLoc" )
    )
VAR _MAX_IGB_NoLoc =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Unite] = MAX ( 'Table'[Unite] )
                && 'Table'[Level2] = MAX ( 'Table'[Level2] )
                && 'Table'[Type] = "IGB_NoLoc"
                && 'Table'[Value] > 10
        )
    )
RETURN
    IF ( _IGB_NoLoc = _MAX_IGB_NoLoc, 1, 0 )

    Add this measure into visual level filter of matrix and set it show items when value =1.

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

    • robertosangi's avatar
      robertosangi
      Icon for Helper I rankHelper I
      4 years ago

      Hi Anonymous thanks for your reply. I want to filter only per the highest value if possibile. Not filtering per 10 value

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi robertosangi ,

         

        I think my measure will show you the highest value in IGB_Noloc. If this still couldn't help you solve your problem, please show me a screenshot with the result you want, this will make me easier to find the solution.

         

        Best Regards,
        Rico Zhou

         

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