Dynamic Ranking

[lherbert501]

Hi,

I need help ranking and getting the top and bottom of my categories for my total measure.

The trouble comes when I want to put it into a single headline row table so I can click the "Top category by total" and the large table below changes dynamically, as I don't believe cards can affect a tablix, and it shows all of that categories data.

I've tried some TopN measures but these are static and don't allow the tablix below to change with it.

Is this possible? I've included below my data. The bottom ranking has to ignore zero's,also.

[v-veshwara-msft]

Hi @lherbert501 ,

Thanks for posting in Microsoft Fabric Community.

You can achieve this by creating two visuals: a selector table and a detail table.

The selector table will only display the top and bottom categories, and when you click on a row there, the detail table will update to show only that category’s data.

Below are the measures used in the setup:

Total1 =
SUM ( CategoryData[Total] )

Top Category Name =
MAXX (
    TOPN ( 1, FILTER ( ALL ( CategoryData[Category] ), [Total1] > 0 ), [Total1], DESC ),
    CategoryData[Category]
)

Bottom Category Name =
MAXX (
    TOPN ( 1, FILTER ( ALL ( CategoryData[Category] ), [Total1] > 0 ), [Total1], ASC ),
    CategoryData[Category]
)

Is Top Or Bottom =
VAR c = SELECTEDVALUE ( CategoryData[Category] )
RETURN IF ( c = [Top Category Name] || c = [Bottom Category Name], 1 )

TopBottom Label =
VAR c = SELECTEDVALUE ( CategoryData[Category] )
RETURN
IF (
    c = [Top Category Name],
    "Top category",
    IF ( c = [Bottom Category Name], "Bottom category" )
)

In the selector table, place Category, TopBottom Label, and optionally Total, then apply a visual-level filter where Is Top Or Bottom = 1. This gives you exactly two rows: the top category and the bottom category. Clicking a row in the selector table will cross-filter the detail table.

Selector Table:

Detail Table:

Detail Table responding to Selector Table:

I have attached .pbix for your reference.

Hope this helps. Please reach out for further assistance.

Thank you.

[Shahid12523]

- Create a disconnected table with “Top Category” and “Bottom Category” labels.
- Use DAX to calculate the actual top/bottom category names dynamically (ignoring zeros).
- Build a measure that returns the selected category based on the label.
- Use a filter measure (ShowCategory = 1) to show only that category’s data in your main table.

[mh2587]

1. Create a Rank measure

Category Rank =
RANKX (
    FILTER ( ALL ( 'Category' ), [Total] > 0 ),   // Ignores zeros
    [Total],
    ,
    DESC,
    DENSE
)

2. Create Top Category (Dynamic) measure 

Top Category =
CALCULATE (
    FIRSTNONBLANK ( 'Category'[Category], 1 ),
    FILTER (
        ALL ( 'Category' ),
        [Category Rank] = 1
    )
)

3. Create Bottom Category (Dynamic) measure

Bottom Category =
CALCULATE (
    FIRSTNONBLANK ( 'Category'[Category], 1 ),
    FILTER (
        ALL ( 'Category' ),
        [Category Rank] = CALCULATE ( MAX ( [Category Rank] ), ALL ( 'Category' ) )
    )
)

4. Place in visuals

• Put Top Category and Bottom Category into a single-row table visual (instead of a card).

• Clicking one will act as a filter for your tablix (main detail table) because it’s a table visual, not a card.

• The Bottom Category will automatically ignore zeros due to the 

  FILTER ( ALL ( 'Category' ), [Total] > 0 ).

[lherbert501]

Hi @mh2587 ,

Thankyou for this. This seems to be on the right lines. It ranks correctly but when it tries to get the top category it exceeds resources.

Would there be a way to group it by eg customer to ease the load?

Thanks

[FBergamaschi]

I am not sure I understand what you are asking

Can you please show an example of the selections you want to make and the expected outcome?

Anyway, here is how to get waht you are discussing in a column (I do not think this is what you need though)

Here how to get it with a measure

Rank M =

IF (

    ISINSCOPE( 'Fact'[Category] ) && [Total M] <> 0,

    RANKX (

        ALL ('Fact'[Category] ),

        [Total M]

    )

)

Total M = SUM ( 'Fact'[Total] )

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[lherbert501]

Hi @FBergamaschi 

Apologies for not being clearer. 

It would need to be a measure. but I would ideally like a card at the top with the top category e.g. category 1. I want to be able to click on the card at the top that is displaying the top category and a whole tablix visual updates on the fly below to only show data for that category. 

I'm not sure you can do this with a card, but I'm thinking maybe a tablix to go at the top as 1 row that I could select.

[Ashish_Mathur]

Hi,

One cannot click on a card.  What exactly are you trying to do?

[lherbert501]

Hi @Ashish_Mathur ,

Yes, as I suggested maybe a table would work with one line instead?

I want to be able to select both entries at the top and the table below is interactive. 

If I click on Category 1 at the top then the table below only shows Category 1 and 130254.

Rank Dummy Data.pbix

[FBergamaschi]

Hi @lherbert501 

tablix sounds like Reporting Services to me 🙂 

This is another planet...

So please can you show me what you want to do? You cannot filter with a card I am afraid but please show me

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[lherbert501]

Hi @FBergamaschi 

Tablix, table Visual.. All the same 😀

I want to be able to select both entries at the top and the table below is interactive. 

If I click on Category 1 at the top then the table below only shows Category 1 and 130254.

Rank Dummy Data.pbix

[FBergamaschi]

hi @lherbert501 ,

here is my version, so we can discuss based on pbix stuff

https://drive.google.com/drive/folders/1QePQY6IYIrtwCr37GjHiIKObuA197OQG?usp=sharing

Can you tell me if this is OK or you need modifications?

You cannot click on a card to create a filter though

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[lherbert501]

Hi @FBergamaschi 

On your PBIX, if you changed the cards to single line tables, is it not possible to select the single line table that displays top category and use this as the interactive selector for the table?

[FBergamaschi · Accepted solution]

Hello, no it  is not possible as that is the value of a measure

You would like to see in the slicer the best and top categories ?

Can this work? You have in automatic the top category in the slicer

https://drive.google.com/drive/folders/1QePQY6IYIrtwCr37GjHiIKObuA197OQG?usp=sharing

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[lherbert501]

Hi @FBergamaschi 

Okay thankyou for confirming.

Im wondering if its possible to simply put the category field into that 1 line table at the top and put a measure visual filter on the 1 line table. e.g. ranking of the lowest thats not zero and set this to 1?

This way the category would still be dynamic

[FBergamaschi]

Yes indeed, I did that

same link

is that a solution now?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[lherbert501]

Yes that's working now thankyou.

Thankyou for your time on this. Its much appreciated @FBergamaschi