Rank by measure in Direct Query

[nleuck]

Hello All,

I'm trying to rank Branch Locations by a measure that finds the % of products with no sales to total inventory. When I try to create the rank measure in the visual all the locations are ranked #1. I haven't had much luck with the rank function in Power BI but maybe I'm doing something wrong.

Formula: RANKX(ALLSELECTED(table1[Branch Location], CALCULATE(DIVIDE(SUM(table2[no sales value]), SUM(table1[total inventory value])),,DESC,Skip)

I've seen other examples and it works just fine for those users, but can't figure out why it won't work for me.

Any ideas?

Thanks

[amitchandak]

I assume it is only copy-paste mistake and the actual formula is

RANKX(ALLSELECTED(table1[Branch Location]), CALCULATE(DIVIDE(SUM(table2[no sales value]), SUM(table1[total inventory value]))),,DESC,Skip)

There were issues with (.

For Rank Refer these links
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

[nleuck]

@amitchandak 

Thanks for the response, but it still doesn't work.

[amitchandak]

What is the issue? Are getting the same rank. try one with All, in place of allselected and check if is giving expected results

[nleuck]

@amitchandak 

I'm getting the same rank. I even tried ALL instead of ALLSELECTED and got the same results.

[amitchandak]

I tried a similar query on the Direct query on SQL server. and seem have worked

[nleuck]

@amitchandak 

Below I have another example when trying to use RANKX. The left table works just fine, but as soon as I add description to the table it ranks everything as 1. I don't understand why.

Formula: 

Rank by Product = RANKX(ALL('table1'[product_id]), CALCULATE(SUM('table1'[available_value])),, DESC, Skip)

[amitchandak]

When we this category ranking, if we add any other group by, it tries to rank inside that. So as soon as you drag description the rank of product is the inside description, which is only 1.

For Rank Refer these links, if you can find a suitable option
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

[nleuck]

@amitchandak 

That seems like an error in the function. If I'm not referencing the description column in RANKX it shouldn't matter. The function should only use the product id becuase that's what I'm telling it to use in the formula. Just my thought.

[amitchandak]

@Greg_Deckler , @parry2k , Can you please help on this one.

[Greg_Deckler]

Coming up to speed. Sure would be great to have example data that I can use to replicate this. RANKX can be tricky to work with sometimes and little differences can mean the world to it working correctly or not. I realize that you can't share the data @nleuck becaues you have been blanking everything out but some general idea here, does the description make everything unique somehow in the table you are ranking over?

One thing you might try. Create a table variable using the DISTINCT ALL values from your id column. Use ADDCOLUMNS to add your RANKX column and then grab your product ID using MAX and then pluck your rank from this table variable. ?

[nleuck]

@Greg_Deckler 

The description column is the product id description. It's a text field, nothing unique. To me it doesn't make sense that adding the description field to a table visual would then change how the measure RANKX works, especially if I'm not refrencing description in the formula.

[Greg_Deckler]

Yeah, well RANKX is a devilish little blighter of a function. I don't see solving this without sample data. I have beat my head against the wall enough times with RANKX I don't want to do it again without knowing that I am working with the correct representation of the data, it is too maddening.

[nleuck]

@Greg_Deckler 

I even tried creating sample data in an Excel spreadsheet with columns id, desc, and price. I still am getting the same results. Whenever I add the desc column it decide to rank everything as 1.

[Greg_Deckler]

That's great! Can you share the Excel file via OneDrive or box or something? 

[nleuck]

@Greg_Deckler 

Can you copy the data from this? I named the first column id (whole number field), desc (text field), price (currency field).

123	rope	$756.84
456	pipe	$549.28
789	rod	$248.18
1011	knife	$46.50
1213	hatchet	$25.99
1415	bolts	$1.99
1617	screw	$2.99
1819	hammer	$15.99
1920	pliers	$6.49
2021	wood	$11.99

[Greg_Deckler]

Yep! That's great, let me take a look.

[Greg_Deckler]

Oh man, so much easier. Odd though. Simple fix, remove the column reference, not sure why the heck that was causing an issue exactly:

Rank by Product 1 = RANKX(ALL('Table'), CALCULATE(SUM('Table'[price])),, DESC, Skip)

 Attached the PBIX. @marcorusso would be eternally greatful if you could impart your wisdom as to why RANKX function is acting in this manner with a column reference to the ID field versus the full table. 

[nleuck]

@Greg_Deckler 

Okay it worked it the sample as expected, but I'm still having an issue with my original dataset. When I add the description colum to the visual nothing changes which I would expect. The only issue is now the ranking doesn't seem right. It should be exactly the same as the sample data I created. See below.

[Greg_Deckler]

OK, and what is the formula for that Rank measure? Is it just looking at available_value to rank?

[nleuck]

@Greg_Deckler 

Formula: 

Rank by Product = RANKX(ALL('table1'), CALCULATE(SUM('table1'[available_value])),,DESC,Skip)