Rankx Ignoring visual filters

[Anonymous]

Hi All,

I am hoping someone can help me with the rankx dax measure.  Below is a sample table

SourceId	Symbol	Amount	Date
A	A	$7,888.50	01/01/20
B	A	$8,751.00	01/01/20
C	A	$970.75	01/01/20
A	B	$958.32	01/02/20
B	B	$1,303.35	01/02/20
C	B	$4,382.50	01/02/20
A	C	$1,022.00	01/03/20
B	C	$864.60	01/03/20
C	C	$432.10	01/03/20

What i would like to do is rank each symbol based on amount by exchange by symbol.  In other words i am ranking A's only against other A's, B's against other B's etc.  This would have to be dynamic where i can adjust the date and the numbers adjust accordingly.  It could be using the date column or a related calendar table.  I would aslo like to be able to insert a filter at the visual level without the rankings changing.    The bottom represents the info i am looking for I tried to attached a photo of the b visuals but i was getting an error so imagine the tables below as if there are 3 seperate BI table visuals.  Each visual would have a visual level filter for source ID.  Any help would greatly appreciated!

Symbol	Amount	Rank		Symbol	Amount	Rank		Symbol	Amount	Rank
A	7888.5	2		A	8751	1		A	970.75	3
B	958.32	3		B	1303.35	2		B	4382.5	1
C	1022	1		C	864.6	2		C	432.1	3

[harshnathani · Accepted solution]

Hi @Anonymous ,

You can create 3 measures

RankSourceA = IF (MAX('Table'[SourceId]) = "A" ,RANKX(FILTER(ALL('Table'),'Table'[SourceId] = "A"),CALCULATE(SUM('Table'[Amount]))))

RankSourceB = IF (MAX('Table'[SourceId]) = "B" ,RANKX(FILTER(ALL('Table'),'Table'[SourceId] = "B"),CALCULATE(SUM('Table'[Amount]))))

RankSourceC = IF (MAX('Table'[SourceId]) = "C" ,RANKX(FILTER(ALL('Table'),'Table'[SourceId] = "C"),CALCULATE(SUM('Table'[Amount]))))

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)