To show other category apart from top 5 in a stacked bar chart

[uj91]

Top 5 filter is applied on the bar chart. On Legend there is Platform field and percentage spend is on x-axis. y-axis is blank. 

Percenateg spend = 

DIVIDE(

    [Spendl],

    CALCULATE([Spend], ALLSELECTED('Table Name))

)

Any suggestions on how to Add ‘Other’ as the 6th breakout showing the multiple paltforms & corresponding spend percentages

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar question in the attached file.

[v-achippa]

Hi @uj91,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @krishnakanth240, @rajendraongole1 and @danextian for the prompt response. 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[danextian]

Hi @uj91 

The “Other” category must physically exist in the model in order to assign it a value. A Top N measure alone is not sufficient, and a calculated column approach isn’t dynamic, since it won’t recalculate the “Other” group as slicers and filters change. You can create a calculated table for that:

Model_Other = 
UNION (
    ADDCOLUMNS ( DISTINCT ( 'Product'[Model] ), "Sort", 1 ),
    ROW ( "Model", "Other", "Sort", 2 )
)

Create this measure:

Top 5 Models = 
VAR TopX = 5
VAR TopModels =
    TOPN ( TopX, ALLSELECTED ( 'Product'[Model] ), [Sales Amount], DESC )
VAR ModelsWithGrouping =
    ADDCOLUMNS (
        ALLSELECTED ( 'Product'[Model] ),
        "@Value", [Sales Amount],
        "@Group", IF ( 'Product'[Model] IN TopModels, 'Product'[Model], "Other" )
    )
RETURN
    SUMX (
        FILTER ( ModelsWithGrouping, [@Group] IN VALUES ( Model_Other[Model] ) ),
        [@Value]
    )

Please see the attached sample pbix

[uj91]

Thanks !  Can we also make a custom tooltip for the others category showing multiple platforms with their spend % ? 

[v-achippa]

Hi @uj91,

Yes, a custom tooltip is possible using a report-page tooltip, but it requires explicit DAX. Power bi does not automatically expose grouped members created by dynamic Top N logic inside tooltips.

So by creating a tooltip page and a measure that excludes the Top 5 platforms, you can display a detailed breakdown of all remaining platforms and their spend percentages when hovering over ‘Other’.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @uj91,

We wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[rajendraongole1]

Hi @uj91 - Please check the attached pbix file 

Take the reference of the logic used to get the other values

Hope this works.

[krishnakanth240]

Hi @uj91 

We can not add 'Other' category using TOPN functionality.

Using a measure, we can do it.

Rank =

RANKX(ALL( 'Table'[Platform]),[Spend],,DESC)

Platform(Top5 + Other) =

IF([Rank]<=5, SELECTEDVALUE( 'Table'[Platform]),"Other")

[uj91]

Not working. Output getting is blank and other.