Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Using RANKX to create a Calculated Column to create groups for Map Legend

Hello All,

I have a strange data set that is a table of line items, and have turned this into a virtual table for quote, user, and dealership views using a lot of DISTINCT() functions, which I believe may be the source of my frustration. I am trying to create a column of dealership ranks based on their total cost values.

I have created a measure that does this using:

 

Dealers - Rank = RANKX(ALL(Quotes[Dealer - Name]), [Dealer - Order Value])

 

Where

 

Dealer - Order Value = SUMX(DISTINCT(Quotes[Dealer - Name]), SUM([Cost]))

 

When I use this same formula in a Calculated Column, all Ranks are set to 1. In fact, ANY formula I've created for a Calculated Column applies Rank 1 to all rows. I've read this is likely because I'm using aggregate SUMs from Measures that don't work the same in Calulated Columns, but I have not figured out how to resolve this. The green text in Ranks is what I would like my column to look like if possible.
Below is a simplified example set of the data I am using, and what I need is a column ranking the Dealerships based on their Cost.

Line Item #Quote #DealershipUserCostRank
155555Dealer 1Tom$1503
255555Dealer 1Tom$2003
355555Dealer 1Tom$753
175364Dealer 2Jane$504
196857Dealer 3Mike$1802
296857Dealer 3Mike$3502
163454Dealer 4Beth$4801
263454Dealer 4Beth$5401
363454Dealer 4Beth$951

Any assistance with this matter would be greatly appreciated!

Thanks,

spham

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

I figured it out!

 

I was looking at the data the wrong way. I was trying to use a measure in a calculated column expression, which didn't throw a flag until I finally internalized what a calulated column is.. Instead of using a "quote cost" measure as the expression to SUM in RANKX, I needed to just use the Cost column, as there is no point to isolating the quote numbers distinctly when summing their cost value.

 

I don't know if this will help anyone else but I do appreciate this community very much!

 

For those interested, the function I wound up using is:

Dealer Ranks = 
	RANKX(
	FILTER(
		'Quotes',
		'Quotes'[Distributor - Name]=EARLIER('Quotes'[Distributor - Name])
        ), 
	CALCULATE(
		CALCULATE(SUM('Quotes'[Line Item $ - Calculated Extended]), 'Quotes'[Quote - Status]="Ordered", USERELATIONSHIP('Calendar'[Date], 'Quotes'[Quote - Order Date])), 
		ALLEXCEPT('Quotes', 'Quotes'[Dealer - Name])
	),,,
    DENSE
)

 

If anyone has any questions about the above DAX, let me know, as there are specifics that only make sense in the context of my data. The big revelations were using the FILTER+EARLIER combination to have sub category rankings, and the DENSE parameter at the end.

 

Thanks All,

spham

View solution in original post

Hi @Anonymous ,

 

I would normally use a formula similar to below to rank a colum.

Sort = 
RANKX (
    VALUES ( 'Table'[Column] ),
    CALCULATE (
        MIN ( 'Table'[Value] ),
        ALLEXCEPT ( 'Table', 'Table'[Column] )
    ),
    ,
    ASC,
    SKIP
)

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

I figured it out!

 

I was looking at the data the wrong way. I was trying to use a measure in a calculated column expression, which didn't throw a flag until I finally internalized what a calulated column is.. Instead of using a "quote cost" measure as the expression to SUM in RANKX, I needed to just use the Cost column, as there is no point to isolating the quote numbers distinctly when summing their cost value.

 

I don't know if this will help anyone else but I do appreciate this community very much!

 

For those interested, the function I wound up using is:

Dealer Ranks = 
	RANKX(
	FILTER(
		'Quotes',
		'Quotes'[Distributor - Name]=EARLIER('Quotes'[Distributor - Name])
        ), 
	CALCULATE(
		CALCULATE(SUM('Quotes'[Line Item $ - Calculated Extended]), 'Quotes'[Quote - Status]="Ordered", USERELATIONSHIP('Calendar'[Date], 'Quotes'[Quote - Order Date])), 
		ALLEXCEPT('Quotes', 'Quotes'[Dealer - Name])
	),,,
    DENSE
)

 

If anyone has any questions about the above DAX, let me know, as there are specifics that only make sense in the context of my data. The big revelations were using the FILTER+EARLIER combination to have sub category rankings, and the DENSE parameter at the end.

 

Thanks All,

spham

Anonymous
Not applicable

Hi @Anonymous,

You can create one measure as below:

Rank = RANKX(ALL('Quotes'[Dealership]),CALCULATE(SUM(Quotes[Cost]),ALLEXCEPT(Quotes,Quotes[Dealership])),,ASC)

Rank.JPG

Best Regards

Rena

Anonymous
Not applicable

Hello Rena,

 

I appreciate the speedy response, but I currently have a measure that does this. I need a Calculated Column of these values so that I can create a group off of it for ranks 1-5, as I need a Top 5 group to apply to the legend of the Map visual.

 

Thanks again!

spham

amitchandak
Super User
Super User

For Rank Refer these links, if they can help
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Hello @amitchandak ,

 

After finding my solution, I realized that there was a new issue that I would have, but your articles provided the solution! There are also two clients for the distributors, and we do need to do nested ranking between clients. The first article covered that beautifully!

 

I don't think I should accept it as an answer to this topic, but those resources are invaluable!

 

Thanks again,

spham

Hi @Anonymous ,

 

I would normally use a formula similar to below to rank a colum.

Sort = 
RANKX (
    VALUES ( 'Table'[Column] ),
    CALCULATE (
        MIN ( 'Table'[Value] ),
        ALLEXCEPT ( 'Table', 'Table'[Column] )
    ),
    ,
    ASC,
    SKIP
)

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Anonymous
Not applicable

Hello @amitchandak ,

 

That is some great documentation, and I appreciate the support. However it is all information I've uncovered in the community here trying to solve this issue. 

 

I am trying to build a calculated column, which only appears in the first of your 3 links, and I already have a measure that performs this function. According to everything I've read, a calculated column that returns the ranks I'm looking for should be something like:

 

Dealers - Ranked = RANKX(ALL(Dealers), SUMX(DISTINCT(Dealers), [Cost]))

 

However this returns all ranks as 1. Please let me know if there is anything else I can do!

 

Thanks,

spham

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.