cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Super User
Super User

Re: Text calculations and rank function

@markymarklondon

 

File attached as well

 

TextRank.png

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

Re: Text calculations and rank function

@markymarklondon

 

File attached as well

 

TextRank.png

markymarklondon Regular Visitor
Regular Visitor

Re: Text calculations and rank function

Thanks everyone! This is great