Rank/RankX of Measure in a Matrix

[kc_1116]

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

[OwenAuger · Accepted 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:

• Rows = 'Asset Class Rank'[Rank]
  
  • Narrow the row headers column until it is hidden.
• Columns = Returns[Year]
• Value = [Periodic Table Label]
  • Conditional formatting background colour: Field value based on measure Periodic Table Background Colour
  • Conditional formatting font colour: Field value based on measure Periodic Table Text Colour
• The calculation item above applied as a visual-level filter.

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