Top N and Others for Field Parameters

[Gerogi27]

Hi there,

There's a few different ways of displaying Top N values and then grouping the other values into an "Others" category. However, I am unsure how to do this using field parameters. I have multiple fields in my field parameter (Ex. country, incoterms, material, plants etc.) that I'd like to display in this format. When a field is chosen, I'd like to display the Top 3 (better to be dynamic, however) values and then group the rest into an "Others" category. 

I tried using multiple tables for each field parameter, and a lot of variables, but it's loading really really slow, and in PBI Service it's not even showing (the error I get is : visual has exceeded the available resources). 

The tutorial I followed is this one: https://www.youtube.com/watch?v=nVvlEHKr_0o

Below you can see my formula which works, but I need something simpler to load easier and be visible in PBI Service as well:

Contracted Quantity for Ranking =

VAR QtyofAll=

            If(SELECTEDVALUE('0. Parameter - Quantities 2'[Parameter]) = "Incoterms 1",

                         CALCULATE([Contracted Quantity],REMOVEFILTERS('z. Slicer table Incoterm 1'[Incoterms 1])),

             If(SELECTEDVALUE('0. Parameter - Quantities 2'[Parameter]) = "Plant",

                        CALCULATE([Contracted Quantity],REMOVEFILTERS('z. Slicer table plant'[Plant])),

           If(SELECTEDVALUE('0. Parameter - Quantities 2'[Parameter]) = "Country of Origin",

                        CALCULATE([Contracted Quantity],REMOVEFILTERS('z. Slicer table Origin country'[Country of origin])),

)))

RETURN  

             IF( ISINSCOPE('z. Slicer table Incoterm 1'[Incoterms 1] ) ||  ISINSCOPE('z. Slicer table Origin country'[Country of origin]) || ISINSCOPE('z. Slicer table plant'[Plant])),

   VAR ParamtoRank = 'Top Parameter'[Top Param Value]

   VAR IsOtherSelected =  SELECTEDVALUE('z. Slicer table Incoterm 1'[Incoterms 1])="Others"||

                                          SELECTEDVALUE('z. Slicer table Origin country'[Country of origin])="Others" ||

                                          SELECTEDVALUE('z. Slicer table plant'[Plant])="Others" 

    VAR ParamwithQty2 = ADDCOLUMNS(ALLSELECTED('z. Slicer table Incoterm 1'[Incoterms 1]), "@Qty2" ,[Contracted Quantity])

    VAR TopParam2 = TOPN(ParamtoRank,ParamwithQty2,[@Qty2])

    VAR QtyofTopParam2 = SUMX(TopParam2,[@Qty2] )

    VAR Result2 = IF(IsOtherSelected , QtyofAll-QtyofTopParam2,'- Measures Contracts'[Contracted Quantity])

VAR ParamwithQty4 = ADDCOLUMNS(ALLSELECTED('z. Slicer table Origin country'[Country of origin]), "@Qty4" ,[Contracted Quantity])

    VAR TopParam4 = TOPN(ParamtoRank,ParamwithQty4,[@Qty4])

    VAR QtyofTopParam4 = SUMX(TopParam4,[@Qty4] )

    VAR Result4 = IF(IsOtherSelected , QtyofAll-QtyofTopParam4,'- Measures Contracts'[Contracted Quantity])

   VAR ParamwithQty8 = ADDCOLUMNS(ALLSELECTED('z. Slicer table plant'[Plant]), "@Qty8" ,[Contracted Quantity])

    VAR TopParam8 = TOPN(ParamtoRank,ParamwithQty8,[@Qty8])

    VAR QtyofTopParam8 = SUMX(TopParam8,[@Qty8] )

    VAR Result8 = IF(IsOtherSelected , QtyofAll-QtyofTopParam8,'- Measures Contracts'[Contracted Quantity])

     RETURN

If(SELECTEDVALUE('0. Parameter - Quantities 2'[Parameter]) = "Incoterms 1", Result2,If(SELECTEDVALUE('0. Parameter - Quantities 2'[Parameter]) = "Country of Origin", Result4, If(SELECTEDVALUE('0. Parameter - Quantities 2'[Parameter]) = "Plant",Result8,

      QtyofAll)

))))

Thanks

[lbendlin]

You would need to create the "Other" row for all parameter fields, and then you would also need to prevent users from selecting more or less than one field parameter (which kinda defeats the purpose of field parameters).    

Filtering the top products alongside the other products in Power BI - SQLBI

May not be worth the effort?