Matrix - measure as rows, standard column as columns

[PetyrBaelish]

I would like to use a matrix visualisation but require the rows to be controlled by a measure.

The report is for creating league tables - I want to show who was in the top 12 in the league with each league season as a column, the rank as rows and the player as the value. The rank is a measure.

Below is a link to the file containing the relevant data.

There is a table called LeagueFinishes which contains the relevant fields/measures.

https://www.dropbox.com/s/k93eke4yehn4inn/GENERIC.pbix?dl=0

Please see page 1. It contains 2 visuals - the left hand visual (currently filtered to a specific season), shows the rank for each player on that season. The visual to the right is what I want to achieve, but I had to use SeasonPoints  as rows instead of SeasonRank as I can't use SeasonRank.

Can anybody help me to create this matrix visual as required.

[v-cherch-msft]

Hi @PetyrBaelish 

You may try to create a table with below formula and then use it in matrix.Attached sample file for your reference.

LeagueFinishes_1 =
SUMMARIZE (
    LeagueFinishes,
    LeagueFinishes[Player],
    LeagueFinishes[season],
    "Points", SUM ( LeagueFinishes[SeasonPoints] ),
    "Rank", RANKX (
        FILTER (
            ALL ( LeagueFinishes ),
            LeagueFinishes[season] = EARLIER ( LeagueFinishes[season] )
        ),
        CALCULATE ( SUM ( LeagueFinishes[SeasonPoints] ) ),
        ,
        DESC,
        DENSE
    )
)

Regards,

[PetyrBaelish]

Thanks @v-cherch-msft,

It's nearly working, but as per the screenshot below there are some blank values appearing.

The left hand visual is from the LagueFinishes calculated table - it shows that for the 2014 PT2 season, Player14 was ranked 1.

Then on the matrix visual from the calculated LeagueFinishes1 on the right hand side: this has worked perfectly for the first season (2014 PT1), but if you look at the results for 2014 PT2 in the second column, Player13 is ranked number one - but we can see on the left hand visual Player13 was ranked 2nd - Player14 should be 1st. There seems to be several players who are on the left visual that are missing from the right hand visual, It's then either moving some players up a rank or missing other players off, but I can't figure out why.

[v-cherch-msft · Accepted solution]

Hi @PetyrBaelish 

Please check the update attached file.Add a rank column and a measure for player.

Measure 2 = CONCATENATEX(LeagueFinishes_1,LeagueFinishes_1[Player],",")

Regards,