The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I am trying to use a scatter plot to draw a "periodic table" of asset class return by calendar year (similar to the target output picture). In order to do so, I need to first rank the returns in each calendar year. I tried using RANKX but I could not get the expected results (in green letter). Can you shed me some light how can I fix my DAX code please? I have attached my Power BI file in the link below. Thanks in advance.
My Pbix file:
https://limewire.com/d/BpJDt#VVxFzw3wCR
Solved! Go to Solution.
Hi @kc_1116
I would actually recommend using a Matrix visual to get as close as possible to the target output, along with using INDEX to identify the Asset Class with a given rank. It is difficult to position data labels correctly with the existing line chart, and data labels can't be customized to the same extent with a scatter plot.
Updated PBIX with example attached.
Here's what I did:
1. Add Background Colour and Text Colour columns to the 'Asset Classes' table (containing colour hex codes).
2. Create a disconnected Asset Class Rank table with a column Rank containing all possible ranks (1 to 13 in this case). This will be used as the Row field for the matrix visual.
3. Updated CAGR as follows to exclude results when no return data are present:
CAGR =
VAR GrowthFactor =
POWER (
PRODUCTX ( returns, 1 + Returns[Return] ),
12 / MAX ( 12, COUNT ( Returns[Return] ) )
)
RETURN
IF ( NOT ISBLANK ( GrowthFactor ), GrowthFactor - 1 )
4. Create these measures:
Periodic Table Label =
VAR CurrentAssetClass =
SELECTEDVALUE ( 'Asset Classes'[Name] )
VAR CurrentReturn =
FORMAT ( [CAGR], "0.0%" )
VAR Result =
CurrentAssetClass
& UNICHAR ( 10 )
& CurrentReturn
RETURN
Result
Periodic Table Background Colour =
SELECTEDVALUE ( 'Asset Classes'[Background Colour] )
Periodic Table Text Colour =
SELECTEDVALUE ( 'Asset Classes'[Text Colour] )
5. Create a calculation group with a calculation item that filters on the Asset Class corresponding to the current value of 'Asset Class Rank'[Rank].
------------------------------------------
-- Tabular Editor DAX Script
------------------------------------------
-- Calculation Group: 'Filter Asset Class'
------------------------------------------
CALCULATIONGROUP 'Filter Asset Class'[Filter]
CALCULATIONITEM "Filer Asset Class by Rank" =
VAR AssetClassReturn =
CALCULATETABLE (
SUMMARIZECOLUMNS ( 'Asset Classes'[Name], "@Return", [CAGR] ),
ALLSELECTED ( 'Asset Classes' )
)
VAR CurrentRank = SELECTEDVALUE ( 'Asset Class Rank'[Rank] )
VAR Result =
CALCULATE (
SELECTEDMEASURE ( ),
INDEX (
CurrentRank,
AssetClassReturn,
ORDERBY ( [@Return], DESC )
),
REMOVEFILTERS ( 'Asset Classes' )
)
RETURN
Result
6. Create a matrix with:
This is how the visual appears:
Note: If you did want to use your original method, you could rewrite AssetRankInYear as
AssetRankInYear =
VAR AssetClassReturn =
FILTER (
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( 'Asset Classes'[Name] ),
"@Return", [CAGR]
),
ALLSELECTED ( 'Asset Classes' )
),
NOT ISBLANK ( [@Return] )
)
RETURN
RANK (
AssetClassReturn,
ORDERBY ( [@Return], DESC )
)
Hi @kc_1116
I would actually recommend using a Matrix visual to get as close as possible to the target output, along with using INDEX to identify the Asset Class with a given rank. It is difficult to position data labels correctly with the existing line chart, and data labels can't be customized to the same extent with a scatter plot.
Updated PBIX with example attached.
Here's what I did:
1. Add Background Colour and Text Colour columns to the 'Asset Classes' table (containing colour hex codes).
2. Create a disconnected Asset Class Rank table with a column Rank containing all possible ranks (1 to 13 in this case). This will be used as the Row field for the matrix visual.
3. Updated CAGR as follows to exclude results when no return data are present:
CAGR =
VAR GrowthFactor =
POWER (
PRODUCTX ( returns, 1 + Returns[Return] ),
12 / MAX ( 12, COUNT ( Returns[Return] ) )
)
RETURN
IF ( NOT ISBLANK ( GrowthFactor ), GrowthFactor - 1 )
4. Create these measures:
Periodic Table Label =
VAR CurrentAssetClass =
SELECTEDVALUE ( 'Asset Classes'[Name] )
VAR CurrentReturn =
FORMAT ( [CAGR], "0.0%" )
VAR Result =
CurrentAssetClass
& UNICHAR ( 10 )
& CurrentReturn
RETURN
Result
Periodic Table Background Colour =
SELECTEDVALUE ( 'Asset Classes'[Background Colour] )
Periodic Table Text Colour =
SELECTEDVALUE ( 'Asset Classes'[Text Colour] )
5. Create a calculation group with a calculation item that filters on the Asset Class corresponding to the current value of 'Asset Class Rank'[Rank].
------------------------------------------
-- Tabular Editor DAX Script
------------------------------------------
-- Calculation Group: 'Filter Asset Class'
------------------------------------------
CALCULATIONGROUP 'Filter Asset Class'[Filter]
CALCULATIONITEM "Filer Asset Class by Rank" =
VAR AssetClassReturn =
CALCULATETABLE (
SUMMARIZECOLUMNS ( 'Asset Classes'[Name], "@Return", [CAGR] ),
ALLSELECTED ( 'Asset Classes' )
)
VAR CurrentRank = SELECTEDVALUE ( 'Asset Class Rank'[Rank] )
VAR Result =
CALCULATE (
SELECTEDMEASURE ( ),
INDEX (
CurrentRank,
AssetClassReturn,
ORDERBY ( [@Return], DESC )
),
REMOVEFILTERS ( 'Asset Classes' )
)
RETURN
Result
6. Create a matrix with:
This is how the visual appears:
Note: If you did want to use your original method, you could rewrite AssetRankInYear as
AssetRankInYear =
VAR AssetClassReturn =
FILTER (
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( 'Asset Classes'[Name] ),
"@Return", [CAGR]
),
ALLSELECTED ( 'Asset Classes' )
),
NOT ISBLANK ( [@Return] )
)
RETURN
RANK (
AssetClassReturn,
ORDERBY ( [@Return], DESC )
)
User | Count |
---|---|
26 | |
10 | |
8 | |
6 | |
5 |
User | Count |
---|---|
33 | |
13 | |
12 | |
9 | |
8 |