Rankx avoid visual slicer

[Anonymous]

Hi There

I Have two table Sale & Region, what i want todo is Averaging the BranchID value and then use rankx for making rank the branchID.

Avg = CALCULATE(AVERAGE(Sale[Value]))

Rank = rankx(all(Region[Branch ID]),[Avg],,DESC,Skip)

How i can using the Slicer "Region" with static Rank?

My goals is when i Click Slicer "Region Two" the rank will show like this

 

Branch ID	Avg	Rank
C	17.5	7
D	23	6
F	27	5
G	34.5	4

instead of picture below.

Here are my tables

Sale

Branch ID	Value
A	10
B	20
C	30
D	40
F	50
G	60
H	70
I	80
J	90
A	3
B	1
C	5
D	6
F	4
G	9
H	7
I	3
J	1

Region

Region	Branch ID
One	A
One	B
Two	C
Two	D
Two	F
Two	G
Three	H
Three	I
Three	J

[Anonymous]

Woow Thx, im missing The "Crossjoin" function.

its works

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below picture and the pbix file's link down below.

Rank Measure =

IF (

NOT ISBLANK ( [Avg Measure] ),

RANKX (

CROSSJOIN ( ALL ( Sales[Branch ID] ), ALL ( Region[Region] ) ),

[Avg Measure],

,

DESC

)

)

https://www.dropbox.com/s/41deblwqfg74agi/dindais.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM