Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
Solved! Go to Solution.
@Anonymous
File attached as well
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)
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?
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
File attached as well
Thanks everyone! This is great
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |