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

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

Accepted Solutions
spham
Frequent Visitor

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

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

Super User I
Super User I

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

Hi @spham ,

 

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
)

 






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

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

7 REPLIES 7
Super User IV
Super User IV

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

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





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


Microsoft
Microsoft

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

Hi @spham,

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

spham
Frequent Visitor

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

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

spham
Frequent Visitor

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

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

spham
Frequent Visitor

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

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

spham
Frequent Visitor

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

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

Super User I
Super User I

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

Hi @spham ,

 

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
)

 






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

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors