Multiple columns custom sort with Rank column

[Tiphany01]

Dears,

I have a table below which needs to be sorted in a particular manner:

1) Status column order should be -> on-going, Preparation, Decide

2) Priority column order should be -> P4, P3, P2, P1

Since there can be scenarios where status and priority are same, then the deciding factor should be Expected sales column (Descending order).

Since there can be scenarios where status, priority, Expected sales  are same, then Expected forecast should be considered.

Sample Table:

Product    StatusPriorityExpected SalesExpected Forecast

A	On-going	P1	116	199
B	On-going	P4	441	81
C	On-going	P4	512	59
D	On-going	P4	441	41
E	On-going	P1	54	143
F	On-going	P2	454	172
G	Preparation	P3	671	111
H	Preparation	P4	147	72
I	Preparation	P1	553	175
J	Preparation	P2	43	58
K	Preparation	P3	60	129
L	Decide	P2	476	28
M	Decide	P2	286	115
N	Decide	P2	476	102

Desired output:

Aim 1 -- To build a custom rank column which helps in sorting based on the requirement.

Aim 2 -- To build a custom cumulative total of expected sales based on this sorting order.

[PaulDBrown]

What is your measure for [Rank Expected]?

[Tiphany01]

It should be the combination of status, priority, expected sales and expected forecast. Ranking defined based on the way the data is sorted and then ranking to be provided to them

[PaulDBrown]

What I meant is can you post the code for the measure. Also what fields from which tables are you using in the visual? 
if you set up the dimension tables, measures and visual following my example, where are you getting the error?

[PaulDBrown · Accepted solution]

Here is one (rather longwinded) way.

1) Create dimension tables with the order you need for Status and Priority

Next the following measures:

Sum Expected = SUM('fTable'[Expected])

Sum Forecast = SUM(fTable[Forecast])

an index to sort the expected/forecats rank and the corresponding rank measure:

Value for Rank = [Sum Expected] * 100000000000 + [Sum Forecast]

Rank Expected =
IF (
    ISBLANK ( [Sum Expected] ),
    BLANK (),
    RANKX (
        ALLEXCEPT ( fTable, 'Status Table'[Status], 'Priority Table'[Priority] ),
        [Value for Rank]
    )
)

The final Index to use in the final rank to get the cumulative sales

Index = 
VAR _Status = MAX('Status Table'[Order]) * 1000000000000000
VAR _Priority = MAX('Priority Table'[Order]) * 10000000000000
VAR _Expected = [Rank Expected] * 10000000
VAR _Forecast = [Sum Forecast]
VAR _Index = IF(AND(ISBLANK([Sum Expected]), ISBLANK([Sum Forecast])), BLANK(), _Status + _Priority + _Expected + _Forecast)
RETURN
_Index

Cumulative Sales =
VAR _CurrentRank = [Index]
RETURN
    CALCULATE ( [Sum Expected], FILTER ( ALL ( fTable ), [Index] <= _CurrentRank ) )

To get

I've attached the sample PBIX file