RANX X delivering same rank to multiple values

[Anonymous]

I've used RANKX before but with filter. I can not get it to work without Filter. My goal is to simply Rank all customers by Sales. 

Fields used:

Fact_Sales[Sales_Sum]       Measure that Sums Sales

Dim_Customer                   Table (distinct row per Customer linked to Sales by CustomerKey)

Measure

CustomerRank_Sales = RANKX( ALLSELECTED( DIM_Customer ) ,

CALCULATE( SUMX(Fact_Sales, FACT_Sales[Sales_Sum] ) ) , , DESC , Dense )

Filters:

Filtered to reduce rows (filter by SalesTerritory in Filters Pane) 

Filtered for Top 15 CustomerRank_Sales. I am hoping that even if pulling in Territory etc. that the Top 15 Rank will be 15 despite that a Territory rank may start with a Customer ranked 30 over all.

Please don't tell me I have to make a Measure for each Territory and use RANKX with Filter! Please (too many Territories)

Result:

Please and Thank you and I give KUDOS and mark as SOLVED!

[v-stephen-msft]

Hi @Anonymous ,

I think you want your measure to not be affected by other columns. Please try to use ALL function and ALLEXCEPT function.

CustomerRank_Sales = RANKX(ALL('Table'),CALCULATE(SUM('Table'[Sales_Sum]),ALLEXCEPT('Table','Table'[Customer])))

There is a blog you can refer to:

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

Best Regards,

Stephen Tao

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

[Ashish_Mathur]

Hi,

I am not clear about your question.  Share the link of the PBI file and show the result there clearly.

[Anonymous]

@Ashish_Mathur ,

Sorry, I did not see that the Rank numbers disappeared in the above example. I edited it to show the rank number.

I described the data fully. I can't share the file. The Cube has maybe 15 tables including a calendar table. The cube works perfect.

Hopefully that is all you need. 

Thank you much!