Dynamic ranking based on slicer selection

[Anonymous]

Hi All -- Need one help with dynamic ranking based on slicer selection. Link has the PBIX file along with data. What I am trying to see is based on my year / month / quarter selection it should give correct ranking in the table like 1,2,3 ...

PBIX Code and Data Source 

[Ashish_Mathur · Accepted solution]

Hi,

It works fine.  See the highlighted portion in the image

[rishikhurana]

Hi Ashish,

Solution is great however, I have a quick question related to this if you can please answer?

I am making a selection through Slicer/Filters where in the background data I have 42 distinct rows. When I create this rank formula, it assigns rank to all 42 but doesnt change (reduce) if i filter few through selection. Like I am making a selection that results in 8 data points, the column technically assign rank till 8 and not all 42 correct?

Please help?

Thanks

[Ashish_Mathur]

Thank you.  That is an sold post.  Please describe your question, share some data and show the expected result.

[rishikhurana]

Yes this is an old post. You can find my question here where I am not able to get the solution.. If you can please.

https://community.powerbi.com/t5/Desktop/Dynamic-x-axis-numbering-based-on-filtering/m-p/2682275#M93...

Thanks much

[Ashish_Mathur]

The reply by Anonymous has been marked as Answer.  Have you tried that solution?

[rishikhurana]

Yes that is not exactly this solution, solution is for the second part to basically make a graph dynamic and show us visual based on selection.

If i can rank data rows based on selection, I can then use a concatenate column and then use it in the graph to basically disguise the client names on x axis and use Client 1, Client 2 etc based on selection and rank assigned.

[Ashish_Mathur]

Hi,

I do not think that will help either because measures cannot be dragged to the X-axis of a graph.  If you write a calculated column formula, then they will not respond to a change in slicers.

[rishikhurana]

Is it. Ok thanks for your reply.

[Anonymous]

Thank you. That helped

[Ashish_Mathur]

You are welcome.

[smpa01]

@Anonymous  please try this and test the output

Measure 6 =
RANKX (
    CALCULATETABLE (
        VALUES ( Sale[Sales Person] ),
        FILTER (
            ALLSELECTED ( Sale ),
            Sale[Date].[Year] = SELECTEDVALUE ( Sale[Date].[Year] )
        )
    ),
    CALCULATE (
        DIVIDE ( CALCULATE ( SUM ( Sale[Sales] ) ), CALCULATE ( SUM ( Sale[Target] ) ) )
    ),
    ,
    DESC,
    DENSE
)

Big thanks to @Zubair_Muhammad