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
Anonymous
Not applicable

Text calculations and rank function

Hi all

 

Wondering if its possible to do something I used to do in Qlikview which was use the rank( ) function to be able to say which stock/sector/company had the best share price growth over a certain period. 

 

Imagine if I had the following companies and share price growth figures

 

  • Company A : 7.5%
  • Company B: -5.3%
  • Company C: 1.4%

 

I want to create a calculated column or measure that references these objects and says something like: "Company A has the highest share price improvements at 7.5% whilst the worst was Company B with -5.3%" 

 

I would display this in a Card object on Power BI

 

Is this type of thing possible?

 

For easier reference lets say the data table would be based on 2 columns like this

 

Company | Share Price

 

 

 

thank you

1 ACCEPTED SOLUTION

@Anonymous

 

File attached as well

 

TextRank.png


Regards
Zubair

Please try my custom visuals

View solution in original post

5 REPLIES 5
Phil_Seamark
Employee
Employee

HI @Anonymous

 

This calculated column in DAX would give you a new column with values that show the best/worst (you can change the order)

 

Column = RANKX('Table2','Table2'[Share Price])

image.png

You could extend the calculation to return values other than numbers (eg, good, bad etc)


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

Thanks @Phil_Seamark - that's good but I want to put it into a standalone comment not a table. I'm basically trying to create a data driven comment that goes along with my visual. 

 

I tried that RANKX function to do something like this 

 

COMMENT = if(RANKX('TABLE','TABLE'[MEASURE)=1, TABLE[STOCK NAME])

 

This comes up as blank, the intention was to have the output be the name of the STOCK with the number 1 ranked MEASURE. 

 

Is there a way to do this?

 

Hello @Anonymous

 

Try this MEASURE

 

Measure =
VAR HighestPrice =
    MAX ( Table1[Share Price] )
VAR BestCompany =
    CALCULATE (
        MIN ( Table1[Company] ),
        FILTER (
            ALL ( Table1[Company] ),
            CALCULATE (
                RANKX (
                    ALL ( Table1[Company] ),
                    CALCULATE ( SUM ( Table1[Share Price] ) ),
                    ,
                    DESC,
                    DENSE
                )
            )
                = 1
        )
    )
VAR LowestPrice =
    MIN ( Table1[Share Price] )
VAR WorstCompany =
    CALCULATE (
        MIN ( Table1[Company] ),
        FILTER (
            ALL ( Table1[Company] ),
            CALCULATE (
                RANKX (
                    ALL ( Table1[Company] ),
                    CALCULATE ( SUM ( Table1[Share Price] ) ),
                    ,
                    ASC,
                    DENSE
                )
            )
                = 1
        )
    )
RETURN
    BestCompany & " has the highest share price improvements at "
        & ROUND ( HighestPrice * 100, 1 )
        & "% whilst the worst was "
        & WorstCompany
        & " with "
        & ROUND ( LowestPrice * 100, 1 )
        & "%"

Regards
Zubair

Please try my custom visuals

@Anonymous

 

File attached as well

 

TextRank.png


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Thanks everyone! This is great

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.