False Tie in RankX - but only when I select Customer 5!

[WilliamPHII]

I tried adding a basic card to my Power BI file, and what should have taken 5 minutes has stretched to an hour and half.

I have one table. It has maybe 150 unique customers and 12,000 rows with all their invoices.

I made a simple RankX measure: RankX(All(Table[CustomerName]),[Total Sales]). Total sales is a basic "Sum(Table[Invoices]).

When I drop this RankX measure in a table on Power BI, it works like a charm. I add Total Sales, and I can see it is working perfectly.

The problem is when I slice the table for one customer, using the same customer column in the slicer! When I do that, and select for one customer, all is well for Customer ranked 1, 2, 3, and 4. But when I click on the slicer on the customer I know is ranked 5, the table shows it as 6. When I slice for the customer I know is 6, it ALSO shows six. The total sales measure in the same table still plainly shows them as having different total sales. When I move on to Customer 7, 8, and so on, the table shows the correct value. It seems something is seriously wrong with customer 5. And I have no idea what it could be.

I have no blanks in my customer column or invoice column.

In my real model I have the customers as a lookup table. But the same thing was happening using a lookup table, and once I realized it happened when I just used one table, I figured it be easiest to keep relationships out of it and focus on the case of just one table.

Very grateful for anyone that can help or even toss a crumb of an idea my way.

William

 

[WilliamPHII]

I'm posting a link to a post by Marco Russo that explains what I found through painful trial-and-error: RankX does not work well if your data has a bunch of trailing decimal places. (It may work in a table, but when you slice for one item, it may or may not be correct). Just wanted this to be here in case any future people wander through with the same issue I had.

Use of RANKX with decimal numbers in DAX #powerpivot #ssas #tabular - SQLBI

 

[JustynaMK]

Thanks William! This is very useful (as are all Marco's and Alberto's articles / books / trainings and videos - I cannot recommend these guys enough ).