cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Deamo Frequent Visitor
Frequent Visitor

Display rank for current user

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

 

rank_table.jpg

 

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.

 

9 REPLIES 9
Phil_Seamark Super Contributor
Super Contributor

Re: Display rank for current user

Hi @Deamo

 

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Deamo Frequent Visitor
Frequent Visitor

Re: Display rank for current user

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 Super Contributor
Super Contributor

Re: Display rank for current user

Hi @Deamo

 

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Deamo Frequent Visitor
Frequent Visitor

Re: Display rank for current user

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 Super Contributor
Super Contributor

Re: Display rank for current user

Hi @Deamo

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Deamo Frequent Visitor
Frequent Visitor

Re: Display rank for current user

@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 Super Contributor
Super Contributor

Re: Display rank for current user

Hi @Deamo,

 

Could you try this formula?

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

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Deamo Frequent Visitor
Frequent Visitor

Re: Display rank for current user

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 Super Contributor
Super Contributor

Re: Display rank for current user

Hi @Deamo,

 

Thanks for your sharing.

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 48 members 837 guests
Please welcome our newest community members: