Dynamic rankx() with filters

[RMDNA]

Hi all,

I've looked into other rankx() posts, but they seem too simple for what I need. Providing a PBIX would be difficult - this has a lot of tables and data, and I can't possible sanitize everything. If this can't be solved without more data, let me know and I'll have to look elsewhere.

To start, I have a chart that ranks products by their sales volume, and this rank is used to provide a running total.

This rank is provided by:

Rank = rankx(all('Table 1'), 'Table 1'[% of Total])

and % of total by:

% of Total = [Product Sales]/[Total Sales]

This works great when no slicers are selected (the slicers are from many differnt tables), but if a slicer is selected, the chart adjusts the bars for volume, but each item keeps its original rank and running total, so they aren't sorted properly:

The chart is sorted by rank, so if the rank is dynamic to the filters selected, the issue should work itself out.

I know it's a hell of an issue, so thanks in advance.

[MFelix]

Hi @RMDNA,

When you use the ALL formula you ignore the filter you have on your reports, may they be at the visual level, page level or report level. To make your measure work with the selected records you need to use the ALLSELECTED in this case the rank is applied over all records that are within the slicer so the ranking is recalculated. Replace your measure by:

Rank = rankx(allselected('Table 1'), 'Table 1'[% of Total])

This should give what you need.

Regards,

MFelix

[RMDNA]

@MFelix, @Zubair_Muhammad,

Thanks for helping me better understand the ALL() variants - they always were a bit confusing.

Your measures mirror the current rank:

 Does allselected(Table1) take into account all potential slicers from all tables, or just from those specified in the formula? My slicers are coming from all sorts of different locations.

From looking at it more, it looks like my % of Total is off, which is casuing the rank issue. I need to figure that out first.

[MFelix]

Hi @RMDNA,

This depends on the context of your measures, can you please provide some sample data and the filters you want to apply.

Regards,

MFelix

[RMDNA]

@MFelix, @Zubair_Muhammad,

PBIX download

I've attached a sample PBIX. The table names and relationships are the same, the data is fabricated. Hopefully the numbers are valid - if not, they can be edited in Query Editor.

I'm looking for a working rank and running total, both of which respond to the slicers at the top of the page.

Thanks for the help, guys. I know this is a lot of work, and you answer half the questions on the board.

[MFelix]

Hi @RMDNA,

I was looking at your data and got some questions what is the value over wich you want to rank the information (Total Sales - Product table or Sales Quantity - Customer sales) and what do you want to rank the customers or the products?

Regards,

MFelix

[RMDNA]

@MFelix,

I'm looking to rank products by sales quantity.

[Ashish_Mathur]

Hi,

I'd like to help.  Please share (in a Table) the exact numbers that you are expecting.

[Zubair_Muhammad]

@RMDNA

May be

Rank = rankx(allselected('Table 1'[ColumnName]), 'Table 1'[% of Total])

[Zubair_Muhammad]

@RMDNA

Try replacting all with allselected

Rank = rankx(allselected('Table 1'), 'Table 1'[% of Total])