I am trying to provide the Rank of a dimension member relative to the total set of members, however I would like to filter the view to show only the top N. I realize may explanation may not provide all the clarity needed to answer my question so I've attached an example.
The attached workbook has a worksheet called 'Data Bar Graph' that ranks State/City sales (or whatever parameter is selected with Evaluation Metric) by the total for all Categories. A dynamic set is created that shows In/Out for the top N. The 'Data Bar Graph' worksheet also shows the rank of the State/City for each Category as text on the bar graph... For example, Seattle, Washington ranks 3rd overall when Sales is selected as the Evaluation Metric, and Seattle also ranks 3rd in Furniture and Technology when Sales is selected, yet it ranks 4th in Office Supplies.
My question pertains to what happens on the 'Data Bar Graph wFilter' worksheet. When I apply the filter on my dynamic set of Top Geographies, my rank against the total population of State/Cities is now limited to just the top N. For example, Detroit, MI (overall rank of 9) has Technology Sales that give it a rank of 17 against the total population of State/Cities (see the 'Data Bar Graph' worksheet), however in the 'Data Bar Graph wFilter' worksheet, Technology Sales for Detroit, MI rank as 13. The rank in the 'Data Bar Graph wFilter' worksheet is not being calculated against the total population of State/Cities, only the filtered set. I would have thought that since I'm not filtering in context, the LOD rank formula would still pick up the rank against the overall population in the 'Data Bar Graph wFilter' worksheet.
Does anyone have any ideas on how to get the rank to calculate on the total population with a Set Filter being applied?
20160912_Rank_with_LOD.twbx 1.2 MB