Filter table based on 2nd highest value from different column

[ABR002]

I found a few posts on a similar subject, but I can't find something for my exact issue. I have a dashboard where I need to show the top 5 accounts, with 5 tables that show a breakdown of those accounts, 1 for each account. Doing topN is easy, but 2nd-5th not so much. I found some stuff that will show me the 2nd highest number itself, along with the account name with no breakdown, but it can't be used to filter the table. I have already created a 2nd table with the amounts grouped by their account names, and an associated rank table. Here's an example, say that a fast food place wants to know it's top categories, it has this data:

Master Table

Category	SubCat	Sales
Sandwich	Hamburger	100
Sandwich	Cheeseburger	150
Sandwich	Chicken	200
Fries	Small	50
Fries	Medium	100
Fries	Large	75
Soda	Coke	25
Soda	Diet Coke	25
Soda	Sprite	45
Soda	Orange	30
Desert	Ice Cream	100
Desert	Cookie	30
Desert	Brownie	45

We then do a 2nd table with just the categories and totals, grouped in the query editor, and an associated rank column, and create the relationship with the master table via the Category column:

Rank Table

Category	Sales	Rank
Sandwich	450	1
Fries	225	2
Soda	125	4
Desert	175	3

I need to create a table with a breakdown of #2, mind you Fries might not always be #2, so I need it to reflect that, meaning I can't filter by "fries", I also need it to acknowledge the selections from the slicers on the page, so I also can't just filter by #2 in the rank column, as when factors such as region are selected, the #2 rank on the current table might dissappear and there would be no #2 to filter by. The final result would be:

#2 Category

Category	SubCat	Sales
Fries	Small	50
Fries	Medium	100
Fries	Large	75
Total		225

Essentially, a table that gives me the 2nd highest number in the rank table. Thanks for any help, I can clarify further if needed.

[Ashish_Mathur · Accepted solution]

Hi,

See if my solution in this PBI file helps.  You should be able to filter the Rank1 column in the filter pane.

[daxer-almighty]

Here you are:

It's totally responsive to any slicing... but there's a caveat. If the code happens to rank 2 categories the same, you'll get an error in the visuals since some measures will want to return a table instead of a scalar. So, it's now up to you to make sure that the ranking measure always returns different ranks for different categories in all circumstances. Think about it well since slicing and dicing can make any 2 categories return the same total sales amount.

The model is very simple:

DEFINE 

// The base measure
MEASURE Measures_[Total Sales] = SUM( Sales[Sales] )

// The category rank.
// For this to return a rank
// Category must be in scope.
MEASURE Measures_[Category Rank] = 
IF( ISINSCOPE( Sales[Category] ),
    RANKX(
        ALLSELECTED( Sales[Category] ),
        [Total Sales],,DESC,Dense
    )
)

// This one returns the Category
// that's been ranked #1 in the
// current context.
MEASURE Measures_[Rank #1 Category] = 
    CALCULATETABLE(
        FILTER(
            DISTINCT( Sales[Category] ),
            [Category Rank] = 1
        ),
        ALLSELECTED( Sales )
    )

// This one is used in the table that
// displays the details of the #1 category.
// You just have to use it to filter rows
// in the table where this measure = 1.
// Of course, you do it in the Filter Pane
// of the visual/table.
MEASURE Measures_[Rank #1 Category Selector] = 
    INT( SELECTEDVALUE( Sales[Category] ) = [Rank #1 Category] )
    
// This one returns the Category
// that's been ranked #2 in the
// current context.
MEASURE Measures_[Rank #2 Category] = 
    CALCULATETABLE(
        FILTER(
            DISTINCT( Sales[Category] ),
            [Category Rank] = 2
        ),
        ALLSELECTED( Sales )
    )
    
// This one is used in the table that
// displays the details of the #2 category.
// You just have to use it to filter rows
// in the table where this measure = 1.
// Of course, you do it in the Filter Pane
// of the visual/table.
MEASURE Measures_[Rank #2 Category Selector] = 
    INT( SELECTEDVALUE( Sales[Category] ) = [Rank #2 Category] )

Link to the file

[daxer-almighty]

I don't think this is that hard but you have to tell me how you're going to handle ties. If 2 categories receive the same rank due to same amounts... how are you going to resolve this?

[ABR002]

We're actually talking about specific amounts (down to cents) in the hundreds of millions of dollars so ties shouldn't be an issue, but I hadn't really thought about that. I guess if there were, say a tie for 3, I would want to somehow bump one (not sure which) back to 4, not sure if that's the best approach.