Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Gerogi27
Regular Visitor

Top N and Others for Field Parameters

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
1 REPLY 1
lbendlin
Super User
Super User

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?

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.