Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I want to customize the ranking of given below series value.
Series Values: 90, 50, 100, 40, 10, 30, 0, -50, -60, -10, 20, -5
We want the result should be like first highest -ve value set then the highest +ve value set and last zero.
Now like: -60, -50, -10, -5, 100, 90, 50, 40, 30, 20, 10, 0
Rank# 1 2 3 4 5 6 7 8 9 10 11 12
~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~ ~~
-ve value set +ve value set Zero
Solved! Go to Solution.
Hi @mohitchugh
Create measures
Measure = SUM(Table1[series values]) Measure 1 = SWITCH ( MAX ( Table1[Column] ), "-", RANKX ( ALLEXCEPT ( Table1, Table1[Column] ), [Measure],, ASC, DENSE ), "+", RANKX ( ALLEXCEPT ( Table1, Table1[Column] ), [Measure],, DESC, DENSE ), "zero", 1 ) Measure 2 = MAXX(FILTER(ALL(Table1),Table1[Column]="-"),[Measure 1]) Measure 3 = IF(MAX(Table1[Column])="+",[Measure 2]+[Measure 1]) Measure 4 = MAXX(FILTER(ALL(Table1),Table1[Column]="+"),[Measure 3]) Measure 5 = IF(MAX(Table1[Column])="zero",[Measure 1]+[Measure 4]) Measure 6 = SWITCH(MAX(Table1[Column]),"-",[Measure 1],"+",[Measure 3],"zero",[Measure 5])
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @mohitchugh
Create measures
Measure = SUM(Table1[series values]) Measure 1 = SWITCH ( MAX ( Table1[Column] ), "-", RANKX ( ALLEXCEPT ( Table1, Table1[Column] ), [Measure],, ASC, DENSE ), "+", RANKX ( ALLEXCEPT ( Table1, Table1[Column] ), [Measure],, DESC, DENSE ), "zero", 1 ) Measure 2 = MAXX(FILTER(ALL(Table1),Table1[Column]="-"),[Measure 1]) Measure 3 = IF(MAX(Table1[Column])="+",[Measure 2]+[Measure 1]) Measure 4 = MAXX(FILTER(ALL(Table1),Table1[Column]="+"),[Measure 3]) Measure 5 = IF(MAX(Table1[Column])="zero",[Measure 1]+[Measure 4]) Measure 6 = SWITCH(MAX(Table1[Column]),"-",[Measure 1],"+",[Measure 3],"zero",[Measure 5])
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-juanli-msft Thanks for putting the efforts to response my Query.....
and sry for the delay in response from my side.
Still, I face the Issues as per required ranking. Error Screenshot is given below for reference.
Something forgets to mention there, may be found error due that...
* That Series Value is "Measure" value of "Column" based values of data source* Now further I calculate the ranking of "Measure" Series value as mention above query... Thanks again for helping us.
Hi @mohitchugh
If [Series value] is a measure instead of a column, use the following measures (one is a column)
First create a column
Column=
SWITCH (
true(),
[Series Value]<0,"-",
[Series Value]>0,"+",
[Series Value]=0,"zero")
Then create measures
Measure 1 = SWITCH ( [Series Value], "-", RANKX ( ALLEXCEPT ( Table1, Table1[Column] ), [Series Value],, ASC, DENSE ), "+", RANKX ( ALLEXCEPT ( Table1, Table1[Column] ), [Series Value],, DESC, DENSE ), "zero", 1 ) Measure 2 = MAXX(FILTER(ALL(Table1),Table1[Column]="-"),[Measure 1]) Measure 3 = IF(MAX(Table1[Column])="+",[Measure 2]+[Measure 1]) Measure 4 = MAXX(FILTER(ALL(Table1),Table1[Column]="+"),[Measure 3]) Measure 5 = IF(MAX(Table1[Column])="zero",[Measure 1]+[Measure 4]) Measure 6 = SWITCH(MAX(Table1[Column]),"-",[Measure 1],"+",[Measure 3],"zero",[Measure 5])
Best Regards
Maggie
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
124 | |
79 | |
49 | |
38 | |
37 |
User | Count |
---|---|
196 | |
80 | |
70 | |
51 | |
42 |