Forum Discussion

giorgiokatr's avatar
giorgiokatr
Helper V
4 years ago
Solved

Show maximum values per category

i have a table where i want to filter so it shows the maximum values (not value) per category by weight

here is an example 

merchantcategoryweight
mer1a4
mer1a4
mer1a4
mer2a1
mer2a1
mer2a1
sar1b7
sar1b7
sar1b7
sar2b2
sar2b2
sar2b2

i want to filter so the table show the 3 rows of mer1 and 3 rows of sar 1

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi giorgiokatr ,

    Please reer to my pbix file to see if it helps you.

    Create a measure.

    bigcategory_ =
VAR _maxvalue =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[category] = SELECTEDVALUE ( 'Table'[category] )
        ),
        'Table'[weight]
    )
RETURN
    IF ( MAX ( 'Table'[weight] ) = _maxvalue, 1, BLANK () )

    Or a column.

    bigcategory_column = 
VAR _maxvalue =
    MAXX (
        FILTER (
             ( 'Table' ),
            'Table'[category] = EARLIER( 'Table'[category] )
            ),
        'Table'[weight]
    )
RETURN
    IF ( ( 'Table'[weight] ) = _maxvalue, 1, BLANK () )

    Then filter the column or measure.

    Best Regards

    Community Support Team _ Polly

     

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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi giorgiokatr ,

    Please reer to my pbix file to see if it helps you.

    Create a measure.

    bigcategory_ =
VAR _maxvalue =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[category] = SELECTEDVALUE ( 'Table'[category] )
        ),
        'Table'[weight]
    )
RETURN
    IF ( MAX ( 'Table'[weight] ) = _maxvalue, 1, BLANK () )

    Or a column.

    bigcategory_column = 
VAR _maxvalue =
    MAXX (
        FILTER (
             ( 'Table' ),
            'Table'[category] = EARLIER( 'Table'[category] )
            ),
        'Table'[weight]
    )
RETURN
    IF ( ( 'Table'[weight] ) = _maxvalue, 1, BLANK () )

    Then filter the column or measure.

    Best Regards

    Community Support Team _ Polly

     

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