Forum Discussion
Solution SageDynamic rankx() with filters
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.
9 Replies
- Zubair_Muhammad
Community Champion
Try replacting all with allselected
Rank = rankx(allselected('Table 1'), 'Table 1'[% of Total]) - MFelix
Super User
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
RMDNAThanks 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.
- Zubair_Muhammad
