How to show top N and other in a stacked column

[Sagejah9]

Hi. Am trying to show top n and others in a chart. I saw a blog on it but it only worked if we use a column or bar chart. 

[v-kkf-msft]

Hi @Sagejah9 ,

My sample data is:

Then I create a new table:

IDs = 
UNION(
    DISTINCT('Table'[ID]),
    {"Other"}
)

        

And create measures to show top 3 and other in a stacked column chart.

SumValue = SUM('Table'[Value])

TOP3_ID = 
VAR IDTopN = 3 
VAR IsOtherSelected =
SELECTEDVALUE ( IDs[ID] ) = "Other"
VAR tab = 
CALCULATETABLE (
    VALUES ( IDs[ID] ),
    ALLSELECTED ( IDs[ID] )
)
VAR _Rank = 
IF(
    ISINSCOPE ( IDs[ID]),
    RANKX (
        tab,
        [SumValue]
    )
)
VAR Top3Values = 
SUMX (
    TOPN (
        IDTopN,
        ADDCOLUMNS (
            tab,
            "@SumValue", [SumValue]
        ),
        [@SumValue]
    ),
    [@SumValue]
)
VAR OtherValues = SUMX ( ALLSELECTED ( 'IDs'[ID] ), [SumValue] ) - Top3Values
RETURN 
IF ( 
    IsOtherSelected, 
    OtherValues, 
    IF ( 
        _Rank <= IDTopN, 
        [SumValue] 
    ) 
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Sagejah9]

Thanks @v-kkf-msft , I will test your approach with my data and let you know as soon as possible. My x axis are months and I'm doing  Last n months approach so I will test how this measure works inside of this approach and It would be good to be able to drill down into other so that we can see the other IDs in your case. 

[v-kkf-msft]

Hi @Sagejah9 ,

Has your problem been solved? If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[parry2k]

@Sagejah9 here is a great article that addresses the same topic. Use this and you will be able to achieve the result you are looking for.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡