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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DivyaReddy
Frequent Visitor

RankX for multiple columns with percentage and numbers

Dear PBI enthusiasts!

I have a requirement where I need to show the Top N of a column '%Profit' and if there is a tie then I need to use the column 'Total Sales'. I am using RankX for my first column but not getting the second column to be used when there is a tie. 

Any help would be greatly appreciated and thank you in advance!

DivyaReddy_0-1666291860207.png

 

1 ACCEPTED SOLUTION

Hi @DivyaReddy ,

try this measure

Rank =

VAR Rank1 =

RANKX(ALL(Facts),

     [% Profit]

)

VAR Rank2 =

    VAR CurrentProfit = [% Profit]

    RETURN

    RANKX(

     CALCULATETABLE(

                   FILTER(

                         ADDCOLUMNS(

                                   Facts,

                                 "@Total Sales",[Total Sales],

                                 "@%Profit",[% Profit]

                                  ),

                        [% Profit] = CurrentProfit

                         ),

              ALL('Facts')),

[Total Sales]

)

RETURN

Rank1*Rank2

 

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

7 REPLIES 7
mangaus1111
Solution Sage
Solution Sage

Hi @DivyaReddy ,

 

try this calculated column:

 

RANK =
VAR _Rank =RANKX('Facts14',[%Profit])
RETURN
COUNTX(
       FILTER(
              'Facts14',
               'Facts14'[%Profit] = EARLIER('Facts14'[%Profit]) && 'Facts14'[Total Sales] > EARLIER('Facts14'[Total Sales])
             ),
       [Total Sales]
) + _Rank
 
Did I answer your question? Mark this post as a solution if I did!

 

This seems to have worked in theory but not sure why I get "Parameter not correct type" for "EARLIER('Facts14'[%Profit])" and EARLIER('Facts14'[Total Sales]) when I substituted my actual column and table names  They They are measures (not columns) in my actual data not sure if that matters?

Hi @DivyaReddy ,

try this measure

Rank =

VAR Rank1 =

RANKX(ALL(Facts),

     [% Profit]

)

VAR Rank2 =

    VAR CurrentProfit = [% Profit]

    RETURN

    RANKX(

     CALCULATETABLE(

                   FILTER(

                         ADDCOLUMNS(

                                   Facts,

                                 "@Total Sales",[Total Sales],

                                 "@%Profit",[% Profit]

                                  ),

                        [% Profit] = CurrentProfit

                         ),

              ALL('Facts')),

[Total Sales]

)

RETURN

Rank1*Rank2

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Looking into it - thank u so much!

HI @DivyaReddy ,

Did I answer your question? Mark this post as a solution if I did!

mangaus1111
Solution Sage
Solution Sage

not very clear for me

Sorry abt that - updated the description and hopefully it is better now. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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