Give a rank to every row in each category

[Dejvis]

Hello everyone,

I'm really need your help. I have a table like this one:

What I need to do is add a rank for every column in every category based on the column with amounts. And then I need to show only rank from 1 to 3 for every category based on the amount.

So I need to show in the table 3 rows with the rank 1-3 based on the amount for each category.

category 1: error with rank 3, error with rank 2, error with rank 1

category 2: error with rank 3, error with rank 2, error with rank 1

category 3: error with rank 3, error with rank 2, error with rank 1

etc.. etc..

Can you help me with that please? When I choose the TOP 3 rows based on the amount it shows only the biggest 3 amounts, but only from the one category taht has the biggest amounts. Not for each category.

Thank you very much.

[Dejvis]

Good morning to you all,

thank you for everything, but I do not think so I can use calculated column as ranking, because the live data are so much bigger. And It is based on date you know.

[Anonymous]

Hi @Dejvis ,

If you need use measure, you can try this DAX:

Rank_measure = 
RANK(DENSE, FILTER(ALL('Table'), 'Table'[Category] = MAX('Table'[Category])), ORDERBY('Table'[Amount], DESC))

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

[Dejvis]

Hello @Anonymous ,

thank you very much for your time and trying to help me. When I tried this on some created test data, it looks like exactly what I want.

but, when I try to do the same on my live data it says: visual exceedid avaliable resources (translated from my language - Czech) and it won't show anything.

I really do not understand with the FILTER(ALL section. Can it be because of it? My live data have a connected calendar where I can choose a specific day an the data will show only for the selected day).

Is it possible that, the FILTER(ALL in the DAX are going around these connections?

When I changed the DAX from ALL to FILTER(ALLSELECTED - but to be honest I really do not know why I did there, but it started to show something, but it is not correct. For example. This data:

this part of data for category "Císař David" and subcategory "D81" are correct. The TOP 1 has amount of 4 and TOP 2 has amount of 1. That is exactly correct.

Look at this part od the same table. Category "Mrštíková Renáta" and subcategory "D80". I can understand why there is more ranks of 5, because the amount is the same, but as a rank 1 (TOP 1) it should be the row with amount of 13 and the Rank has an empty value.

And also this is intereseting. Look at this part of the table:

category "Hrivňák Peter" with 2 subcategories "D47" and "D66". When you look at the "D47" it looks almost fine except the empty rank values.

But for the second category of the same person (category 1 = persons, category 2 = projects that they are responsible for).

It has two rows (R1: Nýtování (STD IBH), R2: Ostatní (Zámek)). First row has amount of 3 and the second row has amount of 1. But why the rank is 2 and 3 and not 1 and 2?

Hope you understand my english a little. If not, please tell me and I will try to describe it again or in any other way.

[Anonymous]

Hi @Dejvis ,

What is your data source, what is your connection model, and how much data do you have? Generally speaking, the reason for the “visual exceeded avaliable resources” error is because the amount of data is too large or the memory consumed by the query or the computation involved exceeds the configured limit.
how to fix visual has exceeded the available resource. - Microsoft Q&A
Solved: Visual exceeded available resources error in Power... - Microsoft Fabric Community

Maybe you can try this DAX, I tested down the performance of that DAX will be a little bit better than the previous DAX I provided, but the difference is not very big:

Measure = var _t = ADDCOLUMNS('Table',"RANK2",RANKX(FILTER(ALL('Table'),[Category]=EARLIER([Category])),[Amount],,DESC,Dense))
RETURN MAXX(_t,[Rank2])

If it is still the same problem, it may be that the amount of data is too large or your computer configuration is not enough to cause, it may be in the DAX changes will not have much effect.

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

[Dejvis · Accepted solution]

Hello, thank you very much for your support. I was yesterday using Chat GPT for help and somehow I make it working with this DAX formula:

Rank =

RANKX(

    ALLSELECTED(InterniVypady[Error]),

    [SUM OF TOTAL AMOUNT],

    ,

    DESC,

    Dense

)

I needed also the ALLSELECTED because of every other FILTER that I have on that page etc..

And now it is working exactly how I needed. The TOP (rank) is based on "Cena" (EN: price).

So I have the main category of "PQA" and then subcategories "Dílna" and for every "Dílna" TOP1-3 values based on the price.

[Irwan]

hello @Dejvis 

then you can change those calculated column into measure.

in new measure, check this DAX.

Rank = 
var _Cat = SELECTEDVALUE('Table'[Category])
Return
RANKX(
    FILTER(
        ALL('Table'),
        'Table'[Category]=_Cat
    ),
    CALCULATE(MAX('Table'[Amount])),,
    DESC,
    Dense
)

then create another new measure with this following DAX.

Top 3 = 
CONCATENATEX(
    FILTER(
        'Table',
        [Rank]=1||
        [Rank]=2||
        [Rank]=3
    ),
    'Table'[Error],
    ", ",
    [Rank],
    DESC
)

3. plot your data in table visual.

Hope this will help.

Thank you.

[Irwan]

hello @Dejvis 

please check if this accomodate your need.

1. create a calculated column for indexing.

Rank = 
RANKX(
    FILTER(
        'Table',
        'Table'[Category]=EARLIER('Table'[Category])
    ),
    'Table'[Amount],,
    DESC,
    Dense
)

2. create a new table with following DAX.

Summarize =
SUMMARIZE(
    FILTER(
        'Table',
        'Table'[Rank]=1||
        'Table'[Rank]=2||
        'Table'[Rank]=3
    ),
    'Table'[Category],
    "Value",
    CONCATENATEX(
        'Table',
        'Table'[Error],
        ", ",
        'Table'[Rank],
        DESC
    )
)

Hope this will help.

Thank you.

[Anonymous]

Hi @Dejvis ,

You can use this DAX to create a calculated column:

Rank = 
RANKX(
    FILTER(
        ALL('Table'),
        'Table'[Category] = EARLIER('Table'[Category])
    ),
    'Table'[Amount],
    ,
    DESC,
    Dense
)

Then in the table visual:

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