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

[Anonymous]

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 #	Dealership	User	Cost	Rank
1	55555	Dealer 1	Tom	$150	3
2	55555	Dealer 1	Tom	$200	3
3	55555	Dealer 1	Tom	$75	3
1	75364	Dealer 2	Jane	$50	4
1	96857	Dealer 3	Mike	$180	2
2	96857	Dealer 3	Mike	$350	2
1	63454	Dealer 4	Beth	$480	1
2	63454	Dealer 4	Beth	$540	1
3	63454	Dealer 4	Beth	$95	1

Any assistance with this matter would be greatly appreciated!

Thanks,

spham

[Anonymous · Accepted solution]

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 · Accepted solution]

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

[v-yiruan-msft]

Hi @Anonymous,

You can create one measure as below:

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

Best Regards

Rena

[Anonymous]

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]

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

[Anonymous]

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

[danextian · Accepted solution]

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
)

[Anonymous]

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