cancel
Showing results for
Did you mean:
Highlighted
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

## Re: Text calculations and rank function

@markymarklondon

File attached as well

5 REPLIES 5
Super Contributor

## Re: Text calculations and rank function

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)

Proud to be a Datanaut!

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

## 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

## Re: Text calculations and rank function

@markymarklondon

File attached as well

Regular Visitor

## Re: Text calculations and rank function

Thanks everyone! This is great