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
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
Solved! Go to Solution.
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!
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?
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 ) & "%"