The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
76 | |
65 | |
52 | |
51 |
User | Count |
---|---|
128 | |
117 | |
78 | |
65 | |
63 |