RANKX() not working over two table on calculated column

[RobbLewz]

My end purpose is to dynamically look at the SUM() values of a customer based on the slicer selection, rank them, for the top 4 show in a chart and combine everyone else greater than rank 4 into an others group for the chart.

First I need to rank them, So I created a measure:

RANKX ( ALL(DimTest[ClientName]), SUMX(RELATEDTABLE(FactTest), [LineTtl]) )

 This works fine, when I look at the measure, it displays the correct rank.

So I turn that into an IF() statement as a calculated column, if rank < 4, client name, else other; however it isn't correctly grouping the "Others" or displaying the rank 4 names.

To debug, I used the above measure as a calcualted column to see what it was evaluating, and I found it was somehow doing a LINE COUNT of all the entries,  in the FACT table rather actually SUM and Ranking??

Here is some sample data

DimTest
Id	ClientName	SectionId
1	Client A	14888
2	Client B	18419
3	Client C	16674
4	Client D	28165
4	Client D	19470
4	Client D	20018
4	Client D	12012
4	Client D	36822
4	Client D	30691
4	Client D	71145
4	Client D	15058
4	Client D	78461
2	Client B	19966
2	Client B	10394
2	Client B	22573
2	Client B	21171
2	Client B	30155
1	Client A	16291
1	Client A	14478
1	Client A	20726

FactTest
SectionId	LineTtl
14888	1500000
18419	500000
16674	10000000
28165	444444
19470	444444
20018	444444
12012	444444
36822	444444
30691	444444
71145	444444
15058	444444
78461	444444
19966	500000
10394	500000
22573	500000
21171	500000
30155	500000
16291	1500000
14478	1500000
20726	1500000

SQL code to generate Data

SET NOCOUNT ON; SET DATEFORMAT DMY;
GO

DROP TABLE IF EXISTS dbo.DimTest;
GO
CREATE TABLE dbo.DimTest
	( Id INT NOT NULL
	, ClientName VARCHAR(100) NOT NULL
	, SectionId  INT NOT NULL);
GO
DROP TABLE IF EXISTS dbo.FactTest;
GO
CREATE TABLE dbo.FactTest
	( SectionId INT NOT NULL
	, LineTtl INT NOT NULL)
GO

DECLARE @table TABLE 
		( Id INT NOT NULL
		, ClientName VARCHAR(100) NOT NULL
		, SectionId INT NOT NULL
		, LineTtl INT NOT NULL);

WITH	CTE
		(Id, ClientName, Cnt, Total, x)
AS		(
		SELECT 1, 'Client A', 4, 6000000, 1
			UNION ALL
		SELECT	2, 'Client B', 6, 3000000, 1
			UNION ALL
		SELECT	3, 'Client C', 1, 10000000, 1
			UNION ALL
		SELECT	4, 'Client D', 9, 4000000, 1
		)
,		_re
		(Id, ClientName, Cnt, Total, x, SectionId, lineTtl)
AS		(
		SELECT	Id, ClientName, Cnt, Total, x, LEFT(ABS(CHECKSUM(NEWID())), 5), c.Total / c.Cnt
		FROM	cte AS c
				UNION ALL
		SELECT	Id, ClientName, Cnt, Total, x + 1, LEFT(ABS(CHECKSUM(NEWID())), 5), _re.lineTtl
		FROM	_re
		where	x < cnt
		)

INSERT	@table
		( 
		Id, ClientName, SectionId, LineTtl
		)
SELECT	Id, ClientName, SectionId, lineTtl
FROM	_re;


INSERT	dbo.DimTest (Id, ClientName, SectionId)
SELECT	Id, ClientName, SectionId FROM @table;

INSERT	dbo.FactTest (SectionId, LineTtl)
SELECT	SectionId, lineTtl FROM @table


SELECT * FROM dbo.DimTest;
SELECT * FROM dbo.FactTest

[MFelix · Accepted solution]

Hello @RobbLewz ,

Following the blog post below, the taxi achieve the final result:

https://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/

See the attach file that was created based on this solution and your only data question is that I made the rank value below 2 that you need to change to 4.