Rank for Hierarchy in Matrix visual not working

[kulpowerbi]

I am looking for solultion that find ranking even when we have Hirarchy. But on my method ranking is only working for one category in matrix, rest are showing rank as one onlye. Attached please see the screenshot and mesure details. 

Measures:

%To GoalRank_Ofc = RANKX(ALLSELECTED(Clinics[Office]),[%To Goal],,DESC,Dense)

%To GoalRank_Rgn = RANKX(ALLSELECTED(Clinics[Region]),[%To Goal],,DESC,Dense)

%To GoalRank_Cnt_Rgn = IF(ISINSCOPE(Clinics[Office]),[%To GoalRank_Ofc],[%To GoalRank_Rgn])

'

[Anonymous]

Hi @kulpowerbi ,

Based on your description, you can create a measure as follows.

Measure =

var x1=RANKX(ALL('Table'[Region]),CALCULATE(SUM('Table'[%-Goal])),,DESC)

var x2=RANKX(FILTER(ALL('Table'[Office],'Table'[Region]),[Region]=MAX('Table'[Region])),CALCULATE(SUM('Table'[%-Goal])),,DESC)

return

IF(HASONEVALUE('Table'[%-Goal]),x2,x1)

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kulpowerbi]

Thank you @Anonymous for posting the solution, I will try out this way and revert back. 

[amitchandak · Accepted solution]

@kulpowerbi , Thanks for sharing the file. Please try a formula like

%To GoalRank_Ofc = RANKX(FILTER(ALLSELECTED(Clinics[Office],Clinics[Region]),Clinics[Region] =max(Clinics[Region])),[%To Goal],,DESC,Dense)

[kulpowerbi]

@amitchandak  In the sample file it's working fine for me too. I have also tried Page level filter and visual level filter on and still on sample file everything is good. 

ON my actual table: I have some Infinty and NAN values, which I am hiding by page level filter, plz see the screenshot:

Region	Office	%-Goal
North	HUD	263.10%
North	LOL	105.37%
North	NPR	310.29%
North	SH	160.24%
North	TARPON	204.52%
South	TPA	281.35%
South	STPETE	259.57%
South	SEM	237.42%
South	PC	84.02%
South	VALRICO	78.37%
West	aa	10.00%
West	bb	15.00%

[amitchandak]

@kulpowerbi , at first look measure seems correct.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[kulpowerbi]

@amitchandak  Here is the sample file attached in google drive link:

Google Drive Link