Grouping products together by count less than 5 = other

[Jay2022]

I'm trying to build a bar chart of products showing all and then grouping all those where teh count is less than 5 into a category called other. Any idea how i can do this ?

[moncx · Accepted solution]

Hey,

there is older post on this topic in this forum, have you tried the solution provided here: Solved: Create miscellaneous/other group using count below... - Microsoft Fabric Community

Hope it helps!

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Jay2022]

Thank you both

[Jihwan_Kim · Accepted solution]

Hi, I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

count measure with other category: = 
VAR _allcount =
    CALCULATE ( [count measure:], REMOVEFILTERS ( product_dimension ) )
VAR _lessthanfiveall =
    SUMX (
        FILTER (
            ADDCOLUMNS (
                ALL ( product_dimension[product], product_dimension[Index] ),
                "@count", [count measure:]
            ),
            [@count] < 5
        ),
        [@count]
    )
VAR _moreorequaltofivelist =
    SUMMARIZE (
        FILTER ( VALUES ( product_dimension[product] ), [count measure:] >= 5 ),
        product_dimension[product]
    )
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( product_dimension[product] ) = "other", _lessthanfiveall,
        SELECTEDVALUE ( product_dimension[product] ) IN _moreorequaltofivelist, [count measure:],
        NOT HASONEVALUE ( product_dimension[product] ), [count measure:]
    )

[Jay2022]

Thank you JIhwan, 

Only I think your solution is perhaps more complex than I need. For example my table as a list of products and that is all) I can group by in the transform data section when i upload the data, therefore i am left with 2 columns Product and Count. 

From here i just want to group any of those counted as less than 5 into 'Other' not sure i needed the table with product index etc 

I'm hoping there may be a simpler solution

[moncx · Accepted solution]

Hey,

there is older post on this topic in this forum, have you tried the solution provided here: Solved: Create miscellaneous/other group using count below... - Microsoft Fabric Community

Hope it helps!

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.