Top n Filter with Dynamic fields

[randeep]

I have a table on TOP 10 filters with different measures. Would like to dynamically change the TOP 10 list based on the measure field selection.

E.g. View top 10 products by COGS.

View top 10 products by Sales.

Hence adding paramenter dynamically into the "top n by field" list.

There would be a slicer for each measures up there, e.g. COGS, Sales, Revenue, SOH, SOO

[amitchandak · Accepted solution]

First create a Rank measure on for these these. The create a slicer and based on slicer filter the values.

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

Slicer :https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[v-kelly-msft · Accepted solution]

Hi @randeep ，

First you need to create a slicer table to put all headers of the columns you wanna filter to one column. Such as below:

 

Then create a table contains all the serial number, number>=N, such as below:

Then you need 2 measures as below:

Rank = 
IF (
    HASONEVALUE ( 'Slicer Table'[Value] ),
    SWITCH (
        SELECTEDVALUE ( 'Slicer Table'[Value] ),
        "Foreast Sales", IF (
            RANKX (
                ALLSELECTED ( 'Table' ),
                CALCULATE ( SUM ( 'Table'[Foreast Sales] ) ),
                SUM ( 'Table'[Foreast Sales] ),
                DESC,
                DENSE
            ) <= [TopN Value],
            1,
            -1
        ),
        "Foreast Revenue", IF (
            RANKX (
                ALLSELECTED ( 'Table' ),
                CALCULATE ( SUM ( 'Table'[Foreast Revenue] ) ),
                SUM ( 'Table'[Foreast Revenue] ),
                DESC,
                DENSE
            ) <= [TopN Value],
            1,
            -1
        ),
        "Actual Sales", IF (
            RANKX (
                ALLSELECTED ( 'Table' ),
                CALCULATE ( SUM ( 'Table'[Actual Sales] ) ),
                SUM ( 'Table'[Actual Sales] ),
                DESC,
                DENSE
            ) <= [TopN Value],
            1,
            -1
        ),
        "Actual Revenue", IF (
            RANKX (
                ALLSELECTED ( 'Table' ),
                CALCULATE ( SUM ( 'Table'[Actual Revenue] ) ),
                SUM ( 'Table'[Actual Revenue] ),
                DESC,
                DENSE
            ) <= [TopN Value],
            1,
            -1
        )
    ),
    1
)

TopN Value = SELECTEDVALUE('TopN'[TopN], 10)

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,

Kelly

[randeep]

@v-kelly-msft Thanks for providing the sample project. It gave quick turn around for me.
@amitchandak Your ranking by category, allowed me to take the dashboard to next step.

Thanks both.

[amitchandak · Accepted solution]

First create a Rank measure on for these these. The create a slicer and based on slicer filter the values.

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

Slicer :https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601