The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi,
I'm struggling to do dynamic ranking which changes based on my filters. I've managed to do a ranked column which works fine - just can't get my head around creating a measure to do the same.
My data is like this:
List of office locations with a % attached:
e.g
Name % Rank
London 10.2% 4
Manchester 6.7% 2
Birmingham 4.8% 1
Leeds 8.3% 3
Area Table
Area 1 = Manchester, Leeds
I am then connected to an area table so if I filter on for example area 1, it returns Manchester and Leeds and I want the rank to recalculate. I'm connected to a folder data source and have created a new table for the latest figures using lastdate. My files within the folder are just excel which are added to monthly and contain a row for each office location.
I'm trying to write the measure on the newly created last date table.
Thanks in advance for any help!
Solved! Go to Solution.
@Anonymous You may like this: To *Bleep* with RANKX! - Microsoft Power BI Community
Here is the CALCULATE approach:
Rank Measure = RANKX(ALLSELECTED('Table'),CALCULATE(SUM('Table'[%])),,DESC)
And the No CALCULATE approach:
Rank No Calculate Measure =
VAR __Name = MAX('Table'[Name])
VAR __Table =
ADDCOLUMNS(
ALLSELECTED('Table'),
"__Rank", RANKX(ALLSELECTED('Table'),[%],,DESC)
)
VAR __Result = MAXX(FILTER(__Table,[Name] = __Name),[__Rank])
RETURN
__Result
Please ignore the previous message, I've fixed it by using the office name from the actual table as opposed the the area table!
Hi,
Sorry in follow up to this question, when I drop the no calculate ranking into my table it resets all other columns until a slicer is selected, for example, I can see a list of office locations but all the %'s return 0% and ranking returns 1.
Any help would be great!
Many thanks
Brill, thank you so much! The no calculate one works perfectly!
I'd tried with the calculate one and it was returning the sum of the filter - e.g. I have 50 offices it dispayed 50 on every line and then if I filtered to 10 it would show that. I thought I was doing something wrong so glad we got the same results for that one!
Thanks again for all your help!
@Anonymous You may like this: To *Bleep* with RANKX! - Microsoft Power BI Community
Here is the CALCULATE approach:
Rank Measure = RANKX(ALLSELECTED('Table'),CALCULATE(SUM('Table'[%])),,DESC)
And the No CALCULATE approach:
Rank No Calculate Measure =
VAR __Name = MAX('Table'[Name])
VAR __Table =
ADDCOLUMNS(
ALLSELECTED('Table'),
"__Rank", RANKX(ALLSELECTED('Table'),[%],,DESC)
)
VAR __Result = MAXX(FILTER(__Table,[Name] = __Name),[__Rank])
RETURN
__Result