Rankx Issue

[collinq]

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!

[edhans]

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.

[edhans · Accepted solution]

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.

[collinq]

Hi @edhans  @TomMartens  and @wdx223_Daniel 

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

[TomMartens]

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

[edhans]

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.

[edhans]

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.

[collinq]

Hi @edhans ,

I have place a sample file here: https://pmps1-my.sharepoint.com/:x:/g/personal/cquiring_epmstrategy_com/EUguVdh5Ai9JkjRQ0nMJ07UByX2T...

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

Thanks!

[wdx223_Daniel]

@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]))