cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
markymarklondon Regular Visitor
Regular Visitor

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

Accepted Solutions
Highlighted
Super User
Super User

Re: Text calculations and rank function

5 REPLIES 5
Phil_Seamark Super Contributor
Super Contributor

Re: Text calculations and rank function

HI @markymarklondon

 

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!

markymarklondon Regular Visitor
Regular Visitor

Re: Text calculations and rank function

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?

 

Super User
Super User

Re: Text calculations and rank function

Hello @markymarklondon

 

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 )
        & "%"
Highlighted
Super User
Super User

Re: Text calculations and rank function

markymarklondon Regular Visitor
Regular Visitor

Re: Text calculations and rank function

Thanks everyone! This is great

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 429 members 4,221 guests
Please welcome our newest community members: