Rank of a specific distributor for display in a KPI or Textbox - value is sometimes off by 1

[thx1200]

The end goal is to have a box that will be in a larger report to show the ranking of a specific distributor, based on total sales over a period of time for specific classes of products.

The data table has these columns: Distributor, Sale Amount, Invoice Date, Product Name, Product Class

The report has three slicers: Distributor, Date Range, and Class

I have created two measures:

- SumOfSales = SUM(Sales[SALES_AMT])

- Rank = RANKX(ALL(Sales[DISTRIBUTOR]),[SumOfSales],,DESC,Dense)

If I create a table visualization and exclude the Distributor slicer, I get a correct representation of what I want in table form.  All distributors are shown and based on the other slicers, I see them all correctly ranked.

Now what I need to do is get that rank out of that visual into a dedicated visual without all the extra stuff.  (ie, JUST the rank, nothing else).

I found there are multiple ways to approach this problem, but what I ultimately settled on, because it worked most reliably, was creating a second Sales table (call it Sales2) that points to the same exact source data source.  Both tables are identical.  I did this because then I can exclude the distributor from the relationship in the data model and I can use a filter for Sales2[Distributor] = SELECTEDVALUE(Sales[Distributor]) as the filter to find the correct rank.  I did this because trying to do it all in a single table was problematic.  Even using filter clearing ALL() or REMOVEFILTERS(), it the rank calculation would always end up as "1" or "2" in the new measure I was creating.

OK, so now onto the SelectedRank measure that I'm trying to calculate. 

SumOfSales = SUM(Sales2[SALES_AMT])

Rank = RANKX(ALL(Sales2[DISTRIBUTOR]),[SumOfSales],,DESC,Dense)

SelectedRank =

VAR sel = SELECTEDVALUE(Sales[Distributor])

RETURN CALCULATE([Rank],

      FILTER(ALL(Sales2[Distributor]),

      Sales2[Distributor] = sel)

)

This works, sometimes.  Half the time, the value is correct, half the time it is 1 more than it is supposed to be and I don't know why.

Example how I mean it "doesn't work" -- I'm comparing the table that shows ALL ranks to the selected rank as I change distributor in the slicer.

DISTRIBUTOR | Rank in the table | SelectedRank

-----------------------------------------------------

Company1     |               1             |     1

Company1     |               2             |     2

Company1     |               3             |     4

Company1     |               4             |     5

Company1     |               5             |     6

Company1     |               6             |     6

Company1     |               7             |     7

Company1     |               8             |     8

Company1     |               9             |     10

Company1     |               10           |     11

Company1     |               11           |     11

Company1     |               12           |     12

etc... No pattern I can tell, it seems random.

[Ashish_Mathur]

Hi,

I cannot understand your expected result.  Share some data to work with and show the expected result clearly.

[v-tangjie-msft]

Hi @thx1200 ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.  

(2) We can create a table as slicer.

Slicer_d = VALUES('Sales'[Distributor]) 

(3) We can create measures.

SumOfSales = SUM('Sales'[Sale Amount])

Rank = 
RANKX(SUMMARIZE(ALLSELECTED('Sales'),[Distributor],"sales",[SumOfSales]),[sales],[SumOfSales],DESC,Dense)

Flag = IF(MAX('Sales'[Distributor]) in VALUES('Slicer_d'[Distributor]),1,0) 

(3) Set filter [Flag=1] and then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[thx1200 · Accepted solution]

Thank you for the suggestion.  We ended up fixing it by rounding all the source data.  The source data could have decimals past what would be expected for currency.  By rounding it to the nearest whole number, the original method worked.  This suggested to me that somewhere deep in PowerBI there was a rounding issue depending on the order things were being calculated.

I did not try your suggestion since that method worked, so I cannot say for certain if it would solve our issue, but I greatly appreciate the time and effort you took to post it and I hope it helps somebody else in the community.