Dynamically group top n to one category and the rest to "other"

[raymondpocher]

Hello community,

I want to dynamically group the top n values into (a) category a = "top n" and (b) all the rest into a category b = "other".

Hence when displaying a bar chart it should always have 2 categories. A filter shoult be there for me to select the top 5, 10, 15, 20 etc. 

In many articles our colleges talk about getting the top n products and grouping the rest into the catgory other. The thing is, it always shows the single product names. Instead I would like to have the top n products combined into one category.

https://goodly.co.in/top-n-and-others-power-bi/ 

Anyone a clou or an article about that topic?

[Padycosmos]

Hope this video helps:

Filter top 3 products with a row for others using DAX in Power BI - YouTube

[johnt75 · Accepted solution]

Not sure if this will work but you could try doing it with a calculation group. Create a numeric parameter so that you can dynamically select how many products to include in the top category, and then create calculation items like

Top N =
VAR TopNValue = [Top N Value]
VAR TopNWithValues =
    TOPN (
        TopNValue,
        ADDCOLUMNS (
            ALLSELECTED ( 'Product'[Product Key] ),
            "@value", SELECTEDMEASURE ()
        ),
        [@value]
    )
VAR Result =
    SUMX ( TopNWithValues, [@value] )
RETURN
    Result

and

Others =
VAR TopNValue = [Top N Value]
VAR TopNWithValues =
    TOPN (
        TopNValue,
        ADDCOLUMNS (
            ALLSELECTED ( 'Product'[Product Key] ),
            "@value", SELECTEDMEASURE ()
        ),
        [@value]
    )
VAR TopNTotal =
    SUMX ( TopNWithValues, [@value] )
VAR TotalValue =
    CALCULATE ( SELECTEDMEASURE (), ALLSELECTED ( 'Product'[Product Key] ) )
RETURN
    TotalValue - TopNTotal