topic Re: Rankx Issue in DAX Commands and Tips

Rankx Issue

collinq — Mon, 30 Nov 2020 22:26:26 GMT

Hi all,

This is a bit embarrassing, but, frankly, I have now confused myself by trying so many variations . I thought this would be easy but obviously I am missing something. I want to rank my customers (CustID field) based on their GrossMargin. AND, based on the date filter. Because I want the date filter, I am using AllSelected in my formula. Trying it with "all" or "allselected" doesn't appear to make a difference in what I have tried though.

GrossMargin is a measure created in the Invoice table.

“Cust Name” is from the Customer table

If relevant: the Invoicetable and the customer table are linked by a different field (CustNum).

This is my goal – Customer with Highest Gross Margin is #1, Second highest, #2, etc.. It should look like this:

Gross Margin	CustID	Gross Margin Rank
805.45	10754	1
709.55	20472	2
605.57	10504	3
584.00	10600	4
437.68	14819	5
358.46	16705	6
237.64	20479	7
234.30	10121	8
223.25	14778	9
120.86	10022	10
110.33	13132	11
85.00	13654	12
79.07	10695	13
66.09	15169	14

This formula:

Gross Margin Rank = RANKX(AllSelected(InvHead),(InvHead[Gross Margin]))

Gets me all “1’s”:

Gross Margin	CustID	Gross Margin Rank
805.45	10754	1
709.55	20472	1
605.57	10504	1
584.00	10600	1
437.68	14819	1
358.46	16705	1
237.64	20479	1
234.30	10121	1
223.25	14778	1
120.86	10022	1
110.33	13132	1
85.00	13654	1
79.07	10695	1
66.09	15169	1

This formula:

Gross Margin Rank = RANKX(ALLSelected(InvHead),Calculate(SUMX(InvHead,InvHead[Gross Margin])))

Gets me this: (which, when sorted by Rank comes out even worse)

Gross Margin	CustID	Gross Margin Rank
805.45	10754	9
709.55	20472	4
605.57	10504	27
584.00	10600	27
437.68	14819	133
358.46	16705	4
237.64	20479	78
234.30	10121	175
223.25	14778	151
120.86	10022	2
110.33	13132	46
85.00	13654	60
79.07	10695	61
66.09	15169	74

Any guidance would be most appreciated.

Thanks!

Re: Rankx Issue

edhans — Mon, 30 Nov 2020 22:54:43 GMT

If you could give us some data to work with it would be helpful. You've pasted some numbers above, but there are no dates, and the format is wonky. I could fix it, but with no dates, we are missing a key component of your ask. See links below for providing data and expected output.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

Re: Rankx Issue

collinq — Mon, 30 Nov 2020 23:12:18 GMT

Hi edhans ,

I have place a sample file here: https://pmps1-my.sharepoint.com/:x:/g/personal/cquiring_epmstrategy_com/EUguVdh5Ai9JkjRQ0nMJ07UByX2Tj6-Zlk81dH2nPo_b3Q?e=fXL906

I am linking "DateField" in Date to "ApplyDate" in InvcHead.

Thanks!

Re: Rankx Issue

edhans — Tue, 01 Dec 2020 00:43:12 GMT

Ok collinq - I am not seeing something. When I do a simple RANKX, I am getting some groupings I don't understand, and rank 8 is missing.

I don't know why it is doing that. Perhaps someone a bit better at DAX would see the issue. TomMartens Greg_Deckler perhaps.

That issue aside, the following measure does rank based on the dates selected in the slicer (see file attached) but again with a few skipped items and a few grouped together.

ALLSELECTED RankX = RANKX( ALLSELECTED(Margin), [Total Gross Margin] ) Total Gross Margin = SUM(Margin[Gross Margin])

For example, selecting April shows this:

I cannot for the life of me figure out where ranked item #2 went. I know why #4 is repeated - same value, But not why #7 is repeated.

Here is my PBIX file. All data is in it, no need to connect to outside files. I had to clean your data up a bit collinq - there was no customer 4661 in your customer table, so filtered that out, and got rid of all zeros and blanks for testing.

Re: Rankx Issue

wdx223_Daniel — Tue, 01 Dec 2020 00:47:48 GMT

collinq if you want a rank according to customers, the first parameter of RANKX should be a table of customer id.

try this.

GM_Rank:=CALCULATE(RANKX(ALL(Customers[CustID]),CALCULATE(SUM(InvcHead[Gross Margin]))),ALLSELECTED(DateTable[Date]))

Re: Rankx Issue

TomMartens — Tue, 01 Dec 2020 07:52:48 GMT

Hey edhans

the answer to your question is not that simple, because of the use of ALLSELECTED 🙂

Using ALLSELECTED as table function removes/blocks all existing filters like April 2020, then the ranking happens across all customers. As there is
no data for the customer 10754 (customerid) in April 2020 rank 2 is missing. The same is valid for rank 8.

Your measure RANKX( ALLSELECTED (facttable) ) returns the lifetime ranking.

I have to admit that I do not fully understand the expected result. From the output table provided, I assume your measure creates the expected result.

Regards,
Tom

Re: Rankx Issue

edhans — Tue, 01 Dec 2020 15:27:37 GMT

thanks Tom. Sometimes it is just tweaking where the filter is placed too.

collinq - after some tinkering, this works:

ALLSELECTED RankX = RANKX( ALLSELECTED(Customers[CustID]), [Total Gross Margin] )

4 is missing below because there is a tie at 3. I reused my simple Total Gross Margin measure here. You could use it inside of the RANKX() as long as you wrap the SUM in CALCULATE if desired.

Re: Rankx Issue

collinq — Tue, 01 Dec 2020 17:31:52 GMT

Hi edhans TomMartens and wdx223_Daniel

Thank you all for the effort and time to help me with this one. I truly appreciate it.

Re: Rankx Issue

edhans — Tue, 01 Dec 2020 19:27:22 GMT

Glad to help out collinq - RANKX() is one of the more finicky functions where you have to make sure you are removing the filters correctly. Add some slicers, and filters, and you often have to tweak the measure to handle the new data.

Hope your project goes smoothly.