Rankx usage with categorical sum

[Anonymous]

I need a measure to calculate the rank of customer's sales. The picture bellow describes the table I have:

Customer	Sales	Date	Rank
A	10	01 Jan 2018	??
A	20	05 Jan 2018	??
B	20	05 Jan 2018	??
B	20	05 Feb 2018	??
A	20	05 Mar 2018	??
C	30	05 Apr 2018	??
C	100	01 May 2018	??

 I have a date slicer in the report and I would like the rank to be depended of the selected date range. For example, if the range is from 01 Janurary - 01 May, this should be the result:

Customer	Sales	Date	Rank
A	10	01 Jan 2018	2
A	20	05 Jan 2018	2
B	20	05 Jan 2018	3
B	20	05 Feb 2018	3
A	20	05 Mar 2018	2
C	30	05 Apr 2018	1
C	100	01 May 2018	1

However if the range selected is 01 Janurary - 05 April, the rank would become: 

Customer	Sales	Date	Rank
A	10	01 Jan 2018	1
A	20	05 Jan 2018	1
B	20	05 Jan 2018	2
B	20	05 Feb 2018	2
A	20	05 Mar 2018	1
C	30	05 Apr 2018	3
C	100	01 May 2018	3

Because only the 100$ sales of customer C would not be considered in the rank formula.

I believe I should use the rankx formula but I have no idea how to include the sum of each customer in the expression. Also I need to solution to be a measure as it needs to be calculated on the go depending on the date range selection

please help. Thanks you

[AlB]

Hi @Anonymous

Can you post the data in text format so that it can be readily copied? Or share the pbix?

[Anonymous]

Appologies! I'm quite new so I don't know the rules around here. The post have been updated. Thanks in advance! 

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Try this measure, please. If you'd like to show all the "C", you need a date table. Please refer to the snapshot.

Measure =
CALCULATE (
    RANKX ( ALL ( Table1[Customer] ), CALCULATE ( SUM ( Table1[Sales] ) ) ),
    ALLSELECTED ( Table1[Date] )
)

Best Regards,
Dale

[Anonymous]

Exacty the thing I want, thank you very much!