How to show dynamic value of top 5 show for each column?

[Anonymous]

Hey everyone,

Thanks a bunch for all your help so far.

I need to make a measure that shows the top 5 values for each column in this image.

The visual should change when I filter the data by any attribute.

Thanks a lot!

[lkalawski · Accepted solution]

Hi @Anonymous,

I don't know your contex and the model you have, but based on the one table you showed, I've included two possible solutions below.
1. Dynamically changing ranking - this means that you will only see columns that are not empty, and additionally, in the future, you can create a slicer that allows the user to manipulate the number of columns (Rank) - for example, show only TOP 3 instead of TOP 5.

To do this, create a column in the table that will determine the ranking for each Branch.

Rank = 
VAR CurrentBranch = 'Raw Data'[Branch]
VAR CurrentAmount = 'Raw Data'[Amount]
RETURN
RANKX (
    FILTER ( 'Raw Data', 'Raw Data'[Branch] = CurrentBranch ),
    'Raw Data'[Amount],
    CurrentAmount,
    DESC,
    DENSE
)

 And then one measure that will dynamically show the values ​​based on the column.

Rank Value = 
CALCULATE (
    MAX ( 'Raw Data'[Amount] ),
    FILTER ( 'Raw Data', 'Raw Data'[Rank] = MAX('Raw Data'[Rank]) )
)

Result:

2. The second option is to create a dynamic ranking checker - instead of creating a column (which in fact increases the size of the dataset), you create a measure for each Rank - the downside here is that you need to know how much Rank you want to show and it is constant.
To do this, you create a measure for each rank:

Rank 1 Value = 
CALCULATE (
    MAX ( 'Raw Data'[Amount] ),
    FILTER (
        'Raw Data',
        RANKX (
            FILTER ( 'Raw Data', 'Raw Data'[Branch] = EARLIER('Raw Data'[Branch]) ),
            'Raw Data'[Amount],
            ,
            DESC,
            DENSE
        ) = 1
    )
)

Similarly for each other.

The Result:

If you have any further questions, feel free to ask.

If I helped, accept the post as a solution and give a kudos. 👍 Thanks! 😁

[Anonymous]

That work very well, Thank a lot!