Forum Discussion
Multiple columns custom sort with Rank column
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.
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 _IndexCumulative Sales = VAR _CurrentRank = [Index] RETURN CALCULATE ( [Sum Expected], FILTER ( ALL ( fTable ), [Index] <= _CurrentRank ) )To get
I've attached the sample PBIX file
4 Replies
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 _IndexCumulative Sales = VAR _CurrentRank = [Index] RETURN CALCULATE ( [Sum Expected], FILTER ( ALL ( fTable ), [Index] <= _CurrentRank ) )To get
I've attached the sample PBIX file
What is your measure for [Rank Expected]?
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
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?
