Top/Bottom N of a Measure by Slicer

[datajunkie_29]

Hello All,

I have a table visual as shown below and all the slicers above:

How can I create a dynamic measure that shows the Top/Bottom 10 by MarginLost based on the slicer selections as shown in the visual above.

eg: if I select a region it should show Top 10  for region or if branch level/any slicer is selected it should do the same.

Any help is appreciated! Thank you. 

[some_bih]

Hi @datajunkie_29 Please share some details. Measure definition

[M_Sum_MargingLost],

and usage in VAR 's could be easly replaced with Sales. Did you do it or...?

[some_bih]

Hi @datajunkie_29 as you wrote "Top/Bottom 10 by MarginLost based on the slicer selections",  as one of possible solutions, I created:

1. Measure for MarginLost as simple sum; 

M_Sum_MargingLost = sum(Sales[MarginLost])

2. Two measures for Top and Bottom based on M_Sum_MargingLost:

2.A For Top:

M_test_TopDyn =

VAR _number_of_values=10    --insert value for top

VAR _ranking =

    RANKX(

        ALLSELECTED(Sales),

        [M_Sum_MargingLost],--choosing Measure

        ,

    desc,                   --choosing Top as desceding

    Dense                   --dense original

    )

VAR _Result=

IF(_ranking<=_number_of_values,_ranking,BLANK())

RETURN _Result

2.B For Bottom:

M_test_BottomDyn MarginLost =

VAR _number_of_values=10    --insert value for top

VAR _ranking =

    RANKX(

        ALLSELECTED(Sales),

        [M_Sum_MargingLost],--choosing Measure

        ,

    ASC,                   --choosing Bottom as ascedin

    Dense                  

    )

VAR _Result=

IF(_ranking<=_number_of_values,_ranking,BLANK())

RETURN _Result

Please aware that in you file there are very few data to test 10 values.

If this helps please kudo.
If this solves your problem please accept it as a solution.

[datajunkie_29]

Hello @some_bih,

Unfortunately it didn't resolve as all the values in the rank were 1 if I did it by Sales. 

[some_bih]

Hi @datajunkie_29  if possible, your file will be needed.   

[datajunkie_29]

Hello @some_bih ,

Here is the sample data file for reference with very limited data.