Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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
Proud to be a Super User!
Paul on Linkedin.
What is your measure for [Rank Expected]?
Proud to be a Super User!
Paul on Linkedin.
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?
Proud to be a Super User!
Paul on Linkedin.
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
Proud to be a Super User!
Paul on Linkedin.
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
84 | |
66 |