Complex Dynamic Ranking

[Allen_R]

I have an enormous data table called enterprise_combined which has vast amounts of data columns for mutiple years, weeks, kinds of business, and geographies.  I mocked up a basic example of the data table below.  I created two separate tables in my model to function as toggles for a measure I created called selected_results.  I've included a snippet of the measure and an exact version of the two toggle tables below as well.

Basically, I have a page that is filtered on 10 of the 13 kinds of business and the year 2025.  The page also has slicers for the user to select the View (Act Counts, Diff to Plan, Diff to PY, and Seas Ratios), Metric (Sales, Returns, and Net Sales), Kind of Business, and Week.  On the page I have a table that simply shows Market Area, Rank, VP, and the Selected Results measure.  The selected_results measure works perfectly given all the different combinations the user can choose from.  The table is sorted by the selected_results column from largest to smallest.  I eventualy want the table sorted by the Rank column once I get it working correctly.  Does anyone have suggestions on how I can modify my ranking measure (which is called top_or_bottom_vp) so that it properly ranks what's being displayed in the table?

A rudimentary mockup of the enterprise_combined data table:

year	week	kind_of_business	market_area	vp	area_name	sales_act	returns_act	net_sales_act	sales_plan	returns_plan	net_sales_plan	sales_py	returns_py	net_sales_py	sales_vs_plan	returns_vs_plan	net_sales_vs_plan	sales_vs_py	returns_vs_py	net_sales_vs_py
2025	1	Retail	Northeast	Tom Selleck	Burlington	450	50	400	425	25	400	460	35	425	25	25	0	-10	15	-25
2025	2	Retail	Northeast	Tom Selleck	Burlington	405	85	320	420	15	405	385	20	365	-15	70	-85	20	65	-45
2025	1	Retail	Northeast	Tom Selleck	Albany	600	75	525	585	35	550	610	45	565	15	40	-25	-10	30	-40
2025	2	Retail	Northeast	Tom Selleck	Albany	720	70	650	695	50	645	701	60	641	25	20	5	19	10	9
2025	1	Retail	Southeast	Bill Elliot	Atlanta	685	80	605	600	75	525	675	35	640	85	5	80	10	45	-35
2025	2	Retail	Southeast	Bill Elliot	Atlanta	680	80	600	655	70	585	670	60	610	25	10	15	10	20	-10
2025	1	Retail	Southeast	John Wayne	Miami	775	70	705	705	80	625	770	75	695	70	-10	80	5	-5	10
2025	2	Retail	Southeast	John Wayne	Miami	695	75	620	710	85	625	705	80	625	-15	-10	-5	-10	-5	-5
2025	1	Wholesale	Northeast	Tom Selleck	Burlington	25550	1500	24050	28105	1775	26330	32105	1285	30820	-2555	-275	-2280	-6555	215	-6770
2025	2	Wholesale	Northeast	Tom Selleck	Burlington	30000	2100	27900	32550	1905	30645	29770	2200	27570	-2550	195	-2745	230	-100	330

toggle_top25_view table:

view

Act Counts

Diff to Plan

Diff to PY

Seas Ratios

toggle_top25_metric table:

metric

Sales

Returns

Net Sales

Snippet of selected_results measure:

selected_results =
VAR SelectedView = SELECTEDVALUE(toggle_top25_view[view])
VAR SelectedMetric = SELECTEDVALUE(toggle_top25_metric[metric])
VAR SelectedKOB = SELECTEDVALUE(enterprise_combined[kind_of_business])

VAR SelectedResults =
	SWITCH(TRUE(),
		SelectedKOB = “Retail” && SelectedMetric = “Sales” && SelectedView = “Act Counts”, SUM(enterprise_combined[sales_act]),
		SelectedKOB = “Retail” && SelectedMetric = “Sales” && SelectedView = “Diff to Plan”, SUM(enterprise_combined[sales_vs_plan]),
		SelectedKOB = “Retail” && SelectedMetric = “Sales” && SelectedView = “Diff to PY”, [sales_%_to_PY],
		“Error”
	)
RETURN
SelectedResults

Current rank measure:

top_or_bottom_vp = 
VAR vpTable = 
    SUMMARIZE(ALLSELECTED(enterprise_combined), enterprise_combined[vp], "_results", [selected_results])
VAR currentResults = 
    CALCULATE([selected_results], VALUES(enterprise_combined[vp])) // Get the current VP context
RETURN
    IF([selected_metric] <> "Returns",
        RANKX(vpTable, [selected_results], currentResults, DESC, DENSE),
        RANKX(vpTable, [selected_results], currentResults, ASC, DENSE)
    )

Below is a snippet of actual results that I'm getting with fake names.  Everything is correct except for the Rank.

Market Area	Rank	VP	Selected Results
Southeastern	2	Bob	88.705
South Central	2	Franky	67,636
Southeastern	3	Willy	67,633
North Central	2	John	66,009
Western	2	Tommy	64,214
South Central	3	Hank	63,913
Southeastern	4	Wilma	61,276
Southeastern	4	Susan	59,052

Any ideas on what I'm doing wrong here?

[lbendlin]

I would consider normalizing the data somewhat, and I would probably also unpivot the metrics to make the selection and calculation easier.  But it will be some effort - not sure if you are willing to invest that.

[Allen_R · Accepted solution]

Yesterday when I posted this question, I couldn't tell if it actually got posted or not because the post woudn't show up anywhere.  At any rate, I ended up figuring out that if I remove market_area from my table visual, the ranking would work appropriately.  The ranking measure was basically ranking the VPs against one another based on the Selected Results BUT by Market Area as opposed to a whole.....if that makes sense.  So I ended up replacing market_area (which is a column name) with a measure that returned the market_area, and the ranking measure worked as it should.  I'm not sure why this worked, but it did.