Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mohitchugh
Frequent Visitor

Ranking of Series

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

 

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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])


6.png

 

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.

View solution in original post

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

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])


6.png

 

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.Capture.JPG

 

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

Dear @v-juanli-msft 

 

All Measure Set But Still found some Error. 

 

Measue Error.JPG

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

August Carousel

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.