cancel
Showing results 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

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
Community Support

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.

4 REPLIES 4
Community Support

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.

Frequent Visitor

@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.

Community Support

If [Series value] is a measure instead of a column, use the following measures (one is a column)

```First create a columnColumn= SWITCH (      true(),     [Series Value]<0,"-",     [Series Value]>0,"+",      [Series Value]=0,"zero")Then create measuresMeasure 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

Frequent Visitor

Dear @v-juanli-msft

All Measure Set But Still found some Error.

Announcements

#### 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.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors