Display rank for current user

[Anonymous]

I have ranked data in a matrix based on volumes & manager name, that filters based on the financial year selected.

What I want to do on a separate details page, is filter all the results to the current manager, by using the USERNAME() formula. The other measures i'm using work fine with this additional filter. The RANK, however, i've been unable to figure out, without using a slicer.

Based on the table above, viewing the details page as Steven, I want to see the rank of 4 for the ETOU GWh - every variation i've tried so far as returned 1 - presumably because it's filtering the entire dataset to Just Steven.

[Phil_Seamark]

Hi @Anonymous

Do you carry the information about the financial year selected through to your details page?  I think this will be needed to re-work out on your details page what Stevens ranking should be.

[Anonymous]

Hi @Phil_Seamark

No, it's not - the financial year is selected via slicers.. is it possible to carry that across pages?

I have a measure calculate what the selected financial year is, which is used in other measures to filter the data.

The etou gwh won measure calculation is this:

ETOU GWh Won = CALCULATE(SUM(WIP_Data[TOU Volume]),
			FILTER('WIP_DATA',WIP_Data[Outcome]="Won"),
			FILTER(ALL(Dates),Dates[FINANCIAL_YR]=MAX(Dates[FINANCIAL_YR])),
			FILTER(WIP_Data,WIP_Data[Fuel ]="Elec"))+0

This is the current Rank measure:

ETOU Rank = RANKX(ALL(Managers[MANAGER_NAME]),[ETOU GWh Won]) 

[Phil_Seamark]

Hi @Anonymous

Slicers can only filter visuals on the same report page as the slicer.  They have no effect on other pages.  Will that be a problem?

[Anonymous]

Not at all, I've put a financial year slicer on the details page as well, so the managers can see how they did in years past

[Phil_Seamark]

Hi @Anonymous

One idea is to create a calcuated table in with data aggregated to just what you need and add the [ETOU GWh Won] and [Rank] formulas as calcuated columns on the calcuated table

[Anonymous]

@Phil_Seamark

I had the same thought, couldn't get the rank working there either as because I want it to rank the mangers by financial year.

I found this post - https://community.powerbi.com/t5/Desktop/How-to-Rank-a-list-based-on-2-values-double-rankX/td-p/4400... not sure how to apply it to what i'm trying to do

[v-yulgu-msft]

Hi @Anonymous,

Could you try this formula?

ETOU Rank = RANKX(ALLSELECTED(Managers[MANAGER_NAME]),[ETOU GWh Won]) 

Regards,
Yuliana Gu

[Anonymous]

Thanks @v-yulgu-msft,

I tried that in a measure and calculated table, both returned 1 for all records.

I've decided to go another way with this report - so rank isn't going to be used any further.

[v-yulgu-msft]

Hi @Anonymous,

Thanks for your sharing.

Regards,
Yuliana Gu