Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
105 | |
69 | |
67 | |
43 |
User | Count |
---|---|
151 | |
103 | |
102 | |
87 | |
63 |