TOP N and Bottom N for 4 different measures

[hemsun]

Hi, 

I am trying to display the top 3 and bottom 3 districts for the vehicle sales growth % with respect to each class (petrol, diesel, electric and others). 

I have created the following measures. 

Top Rank Petrol =

RANKX(

    All(dim_districts[district]),

    [Petrol Vehicles Growth %],

    ,

    DESC

)

Bottom Rank Petrol =

RANKX(

    All(dim_districts[district]),

    [Petrol Vehicles Growth %],

    ,

    ASC

)

Condition Petrol = switch( True(), SELECTEDVALUE(topbottom[Metric])="Top 3" &&[Top Rank Petrol] <=3 ,1, SELECTEDVALUE(topbottom[Metric])="Bottom 3" &&[Bottom Rank Petrol] <=3 ,1, 0 )

I would Like to achieve this using one slicer instead of 4 slicers for 4 different measures. Is there a way to achieve this instead of creating more tables for the metrics (top3 and bottom 3)

[hemsun]

Hi Could you kindly check now

[Ashish_Mathur]

Hi,

I tried but could not solve it.  While i can create a slicer which allows one to select the measures (using field parameters), i cannot use the measure selected in the slicer in another measure.

Hope someone else can help.

[MFelix]

Hi @hemsun ,

You can make use of calculation items that allows to replicate the use of the measure based on selected measure, however in this case a workaround can be the use of parameter fields combined with the update of your ranking measures:

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters 

Top Rank Petrol =
RANKX(
    All(dim_districts[district]),
    SWITCH(SELECTEDVALUE(Parameter[Parameter Order]),0, [Petrol Vehicles Growth %], 1 , [MEasure 2], 3, [Measure3])
    ,
    DESC
)

The parameter allows for you to have a selection for the metric you want to use has a value on the visualization or as a slicer.

[hemsun]

 do you mean that rankx will not perform with measures and will only work with calculated columns. Currently, I am facing this issue. 

I am using the measure below.it removes the blank rows but skips the rank

Rank Petrol Growth % =

if(

    not(isblank( [Petrol Vehicles Growth %])),

    RANKX(

       all( dim_districts[district]),[Petrol Vehicles Growth %], , DESC)

)

[MFelix]

Hi @hemsun ,

I'm not saying that rankx will not work with measures, I'm giving you an alternative RANKX syntax so that you have a single slicer for 4 measures, in this case you would have a slicer for the top 3 bottom 3 and another one for the measure you want to use.

Concerning the question about the fact of the blanks the issue is that blanks are treated has 0 so in this case you get a skip of values from 3 to 5.

Instead of the RANKX have you tried doing use of TOPN?

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[hemsun]

I did not use topn as i want to achieve this 

1. rank the districts 
2. create top 3 and bottom 3 in the matrix
3. want to add Petrol growth, diesel growth, electric vehicles growth , other fuel growth as field parameters

when I select petrol and top3 , it needs to display the petrol growth % and so on. 

this is my pbix file. Please see the tab Transport Analysis

https://drive.google.com/file/d/13fnPQppLcIXuXoCNS4XOsMDiBWWijUlY/view?usp=drive_link

[MFelix]

Hi @hemsun ,

Try the following measure:

Ranking by Fuel =
VAR SelectedMetric =
    SELECTEDVALUE ( topbottom[Metric] )
VAR TypeFuel =
    SWITCH (
        SELECTEDVALUE ( Parameter[Parameter Order] ),
        0, [Petrol Vehicles Growth %],
        1, [Diesel Vehicles Growth %],
        2, [Electric Vehicles Growth %],
        3, [Otherfuel Vehicles Growth %]
    )
VAR topTable =
    IF (
        SELECTEDVALUE ( dim_districts[district] )
            IN SELECTCOLUMNS (
                TOPN (
                    3,
                    FILTER (
                        ADDCOLUMNS (
                            ALLSELECTED ( dim_districts[district] ),
                            "TypeFuelValue",
                                SWITCH (
                                    SELECTEDVALUE ( Parameter[Parameter Order] ),
                                    0, [Petrol Vehicles Growth %],
                                    1, [Diesel Vehicles Growth %],
                                    2, [Electric Vehicles Growth %],
                                    3, [Otherfuel Vehicles Growth %]
                                )
                        ),
                        [TypeFuelValue] <> BLANK ()
                    ),
                    [TypeFuelValue], DESC
                ),
                "dist", dim_districts[district]
            ),
        1
    )
VAR bottomTable =
    IF (
        SELECTEDVALUE ( dim_districts[district] )
            IN SELECTCOLUMNS (
                TOPN (
                    3,
                    FILTER (
                        ADDCOLUMNS (
                            ALLSELECTED ( dim_districts[district] ),
                            "TypeFuelValue",
                                SWITCH (
                                    SELECTEDVALUE ( Parameter[Parameter Order] ),
                                    0, [Petrol Vehicles Growth %],
                                    1, [Diesel Vehicles Growth %],
                                    2, [Electric Vehicles Growth %],
                                    3, [Otherfuel Vehicles Growth %]
                                )
                        ),
                        [TypeFuelValue] <> BLANK ()
                    ),
                    [TypeFuelValue], ASC
                ),
                "dist", dim_districts[district]
            ),
        1
    )
RETURN
    IF ( SelectedMetric = "Top 3", topTable, bottomTable )

Has you can see it returns the result:

You just need to use it has a filter on your visualization.

[Ashish_Mathur]

Access Denied message