Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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

Need Help

5 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    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])

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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?

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

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