Rank based on Month to Date Sales but show Cumulative Sales by Day

[Anonymous]

Hi,

I'm sure this has been covered but I did not find a solution. 

I need to Rank Divisions by Cumulative Month to Date Sales

The Charts will show Cumulative Sales by Day for the top Divisions

The Chart will filter by Division MTD Sales Rank so the first chart on the page is Ranked Division #1 etc.

I have the Cumulative Sales Measure but the RANKX function is ranking by Day, not MTD. That means the chart intended for one Division will show mulktiple based on daily sales rank. 

Again, the Chart is to be filtered by the Division Rank for Month to Date Sales (show only one Division per chart).

Here is my DAX (The Sales value is a Measure of the Sum of Sales):

Division Rank = RANKX (
ALLSELECTED ( Division[DivisionIDName]),
CALCULATE ( ( _Sales[Revenue_LocalCurrency] ) ))

[v-danhe-msft]

Hi @Anonymous,

Could you please post me some sample data and your desired result or share the pbix file if possible?

Regards,

Daniel He

[Anonymous]

The data is very simple: this is made up but it is basically the data

Date	Division	Sales	Division Rank based on MTD Sales
1/1/2018	1	1000	1
2/1/2018	1	1200	1
3/1/2018	1	800	1
4/1/2018	1	900	1
1/1/2018	3	900	3
2/1/2018	3	1100	3
3/1/2018	3	700	3
4/1/2018	3	800	3
1/1/2018	7	950	2
2/1/2018	7	1150	2
3/1/2018	7	750	2
4/1/2018	7	850	2

The Issue is that We want the Division ranked based on the MTD Sales but want to show the Month to Date Sales daily in a chart.  This is a real example clip. Chart 1 shows the #1 ranked Division. It is the largest division so every day it it the #1 rank.  Chart 2 shows what happens when I make a chart of the #2 ranked Division. The #2 rank changes by day so the chart shows multiple Divisions. I want it to show only the #2 ranked Division as of today for each day. The Chart should show only Division 3 for each day as it is currently the #2 ranked Division.

[Anonymous]

@Anonymous

I saw your post regarding RANKX. Maybe you know how to do this. (see original post). 

The  RANKX needs to be told to rank based on Month to Date Sales so it does not rank by the time placed into the chart which is daily.