RANKX breaks when other measures are added to table

[Sar_Jo19]

Hi all,

I'm having issues with what should be a simple ranking measure that I've used in exactly the same format in multiple dashboards before now. I have a "Sales Data" table which has columns for Location, Sales, Transactions, Margin. I've created measures for each value as follows: CALCULATE(SUM('Sales Data'[Sales])), I've then created a measure for ATV which is just: DIVIDE([Sales],[Transactions]).

I want to rank ATV for each location and am using this measure: CALCULATE(RANKX(ALL('Sales Data'[Location]),CALCULATE[ATV],,DESC,Skip))

This works fine when I put the rank into a table along with ATV and Location, but when I drag the Margin measure into the table the ranking breaks as in the examples below

Table Without Margin Measure

Location	ATV	ATV Rank
A	40.21	1
B	34.66	2
C	32.30	3
D	29.87	4
E	29.75	5
F	29.01	6
G	28.55	7
H	28.30	8
I	27.96	9

Table With Margin Measure Added

Location	ATV	ATV Rank	Margin
A	40.21	1	63,020.56
B	34.66	3	59,880.21
C	32.30	3	56,761.30
D	29.87	4	53,500.01
E	29.75	5	55,750.98
F	29.01	5	52,101.04
G	28.55	6	50,908.77
H	28.30	6	49,890.90
I	27.96	9	50,532.65

Like I said I've used this exact measure in the same context on other reports and had no issues, to my knowledge measures should never affect other measures when added to a table visual. Would anyone have any insight as to why this is happening? I'm currently pulling my hair out trying to understand what the issue is.

Thanks so much in advance

[v-yangliu-msft]

Hi  @Sar_Jo19 ,

ALL used as a filter argument is filter modifier. It removes the filter on its argument.

When the argument is a table, it removes the filter over the entire expanded table (the table specified as argument). Whent the argument is one or more columns, it removes the filters over the specified columns.

In the Measure 2, only the filter existing over 'Sales Data'[Location] is removed

You can create a dax using this:

Measure = RANKX(ALL('Sales Data'),CALCULATE(SUM('Sales Data'[ATV])),,DESC,Skip)

If it does not meet the expected results, Can you share sample data and sample output in table format? Or how the Margin Measure is generated, and are there any other columns.

Best Regards,

Liu Yang

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

[johnt75]

As a first step I would use performance analyzer to get a copy of the DAX generated for both versions of the visual - with margin and without. Then have a look at the queries in DAX Studio and see what the differences are.

Although visually you are simply adding another measure it is possible that more is going on behind the scenes, in particular if a filter is being applied to something other than Sales[Location] then that could potentially have an impact

[amitchandak]

@Sar_Jo19 , if you add measure, I do not think rank can change. But it can change if you add column that that is not summarized, Because rank get distributed inside new column

[CarlMontreal]

Now, here is a perfect example of unsolliciated reply and why the fabric community is becoming so difficult to find answers.  "...if you add measure, I do not think rank can change...".  @amitchandak , YES, it happens that the rank changes when adding an additional measure.  This community is NOT about opinions, it is about helping someone with a real problem.  And if you do not understand it, then mayne refrain to reply.