Rankx Value by category hierarchy for each dynamic time period in matrix

[Missy]

Im trying to get this 

but i get this 

here is my current code 

RankbyDate =

VAR Region = ISFILTERED(Station_Departure[xRegion_t])

VAR SubRegion = ISFILTERED(Station_Departure[xSubRegion_t])

VAR Station = ISFILTERED(Station_Departure[xStn_t])

VAR Result = SWITCH ( true(),

Region&&NOT(SubRegion)&&NOT(Station),CALCULATE( RANKX(ALLSELECTED(Station_Departure[xRegion_t]), [TotalAgreementLevelScore],,DESC)),

SubRegion&&NOT(Station)&&NOT(Region),CALCULATE( RANKX(ALLSELECTED(Station_Departure[xSubRegion_t]), [TotalAgreementLevelScore],,DESC)),

Station&&NOT(Region)&&NOT(SubRegion),CALCULATE (RANKX(ALLSELECTED(Station_Departure[xStn_t]), [TotalAgreementLevelScore],,DESC)))

RETURN Result

[Daryl-Lynch-Bzy]

Hi @Missy , could you please upload a sample PBIX file?

[Missy]

Unfortunately I cant but I can give you a picture of the data

I have a Date Table that connects to Event Dates in my “Base” table that has unique responses by ID. Then that table connects to all the responses per unique id per question.  And each unique ID is connected to a meta data table which connects to the category hierarchy.  As shown below.

I then have some measures that calculate a score based on various answers to varies questions

TotalAgreementLevelScore =

….. VARS

VAR TotalAgreementLevelScore = CALCULATE(TotalAgreementLevelMultiplierCount/TotalAgreementLevelCount)

RETURN TotalAgreementLevelScore

Then I calculate the rank based on score by Category Hierarchy

VAR Region = ISINSCOPE(Station_Departure[xRegion_t])

VAR SubRegion = ISINSCOPE(Station_Departure[xSubRegion_t])

VAR Station = ISINSCOPE(Station_Departure[xStn_t])

VAR Result = SWITCH ( true(),

    Region&&NOT(SubRegion)&&NOT(Station),CALCULATE( RANKX(ALLSELECTED(Station_Departure[xRegion_t]), [TotalAgreementLevelScore],,DESC)),

    SubRegion&&NOT(Station)&&NOT(Region),CALCULATE( RANKX(ALLSELECTED(Station_Departure[xSubRegion_t]), [TotalAgreementLevelScore],,DESC)),

    Station&&NOT(Region)&&NOT(SubRegion),CALCULATE (RANKX(ALLSELECTED(Station_Departure[xStn_t]), [TotalAgreementLevelScore],,DESC)))

RETURN Result

Then put the results into a matrix to show each category rank by time period and it does not work.

Also when I try to expand the rows I end up with no results for the sub categories