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 ce...
Need Help
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 )
        & "%"
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Thanks everyone! This is great