Rank with Slicers applied

[MitieNHSFred]

I have an “Init_Scores” table of data, from Excel, which I have used to create a Table visual, showing key data and has 4 slicers on other fields in the table that can be applied jointly or individually.  Some slicer fields come from Excel, others are DAX creations.

In the Excel, based on responses to the questions, I have come up with “Overall Impact” and “Overall Priority” values, which are then used to calculate a “Weighted Score” value.

In Power BI I want to show a ranking for each initiative, based on the “Weighted Score” AND the field slicer(s) in play at that time or no slicers if nothing has been selected.

I’ve tried creating a new

• Column : Init_Rank_C = RANKX(ALLSELECTED(Init_Scores), Init_Scores[Weighted Score], , DESC, Dense)
  
  

• Measure: Init_Rank_M =
                      VAR CurVal = ((SUM(Init_Scores[Overall Impact])/55) * SUM(Init_Scores[Overall Priority])/34)*100
                      RETURN
                      CALCULATE(
                                 RANKX(Init_Scores, [Weighted Score], CurVal, DESC, Dense),
                                 ALLEXCEPT(Init_Scores, Init_Scores[Status]))

But what I find is that, when I apply any/all of the field slicers, the report shows those initiatives I want but

• the Init_Rank_C value remains unchanged and shows the ranking number as if no field slicer(s) had been applied,
• the Init_Rank_M values do appear to change, but the rank values appear to jump, rather than be contiguous, even when there are no intervening rows

I’ve played around with the DAX and either end up with rank showing everything as 1 or what appears to be random numbers that I cannot explain.

Please can anyone help me

Thanks in advance

Fred

[lbendlin · Accepted solution]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[MitieNHSFred]

We ended up going on a completely different approach, putting all the entries into a 4-square MoSCoW view, that gave us what we needed without the need for filtering and ranking.