topic Re: Dynamic Ranking - Measure in DAX Commands and Tips

Dynamic Ranking - Measure

Anonymous — Mon, 27 Feb 2023 15:50:35 GMT

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!

Re: Dynamic Ranking - Measure

Greg_Deckler — Mon, 27 Feb 2023 16:28:08 GMT

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

Re: Dynamic Ranking - Measure

Anonymous — Tue, 28 Feb 2023 07:20:22 GMT

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!

Re: Dynamic Ranking - Measure

Anonymous — Tue, 28 Feb 2023 13:24:48 GMT

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

Re: Dynamic Ranking - Measure

Anonymous — Tue, 28 Feb 2023 13:36:01 GMT

Please ignore the previous message, I've fixed it by using the office name from the actual table as opposed the the area table!