Create "others" in Pie Chart

Hi Applicable88 ,

 

Create a calculated column to take the company name according to the top two in the ranking, and change the other company names to "other". For example:

Column = 
var _rank = rankx('Table',CALCULATE(SUM('Table'[Bookingnumber]),FILTER('Table','Table'[Supplier]=EARLIER('Table'[Supplier]))),,,Dense)
return
IF(_rank<=2,'Table'[Supplier],"other")

Then use this column in pie chart instead of [supplier].

 

Best Regards,

Jay

Here is one way to make it dynamic.

1) create a table with the supplier & a row for "Other".

 

TopN Table = 
VAR Supplier = DISTINCT('Table'[Supplier])
VAR Other = {"Other"}
Return
UNION(Supplier, Other)

 

Create an inactive relationhsip between this table an the supplir filed in the main fact table:

2) now the following measures:

 

Booking number Distinct count = DISTINCTCOUNT('Table'[Bookingnumbers])

Rnk =
IF (
    SELECTEDVALUE ( 'TopN Table'[Supplier] ) = "Other",
    1,
    RANKX (
        ALL ( 'TopN Table'[Supplier] ),
        CALCULATE (
            [Booking number Distinct count],
            USERELATIONSHIP ( 'TopN Table'[Supplier], 'Table'[Supplier] )
        )
    )
)

DistinctCount TopN =
VAR _All =
    CALCULATE ( [Booking number Distinct count], ALL ( 'Table'[Supplier] ) )
VAR Supplier =
    CALCULATE (
        [Booking number Distinct count],
        USERELATIONSHIP ( 'TopN Table'[Supplier], 'Table'[Supplier] )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'TopN Table'[Supplier] ) = "Other",
        _All
            - SUMX (
                FILTER (
                    ALL ( 'TopN Table' ),
                    [Rnk] <= SELECTEDVALUE ( 'TopN Value'[TopN Value] )
                ),
                CALCULATE (
                    [Booking number Distinct count],
                    USERELATIONSHIP ( 'TopN Table'[Supplier], 'Table'[Supplier] )
                )
            ),
        Supplier
    )

Final Measure = IF([rnk] <= SELECTEDVALUE('TopN Value'[TopN Value]), [DistinctCount TopN])

 

This is intended to work on a selection of the TopN value from a slicer (created using the "New Parameter" option under Modeling. If it's to remain static, you can hard code the value instead of the SELECTEDVALUE ( 'TopN Value'[TopN Value] ) in the measures.

 

 

 

I've attached a sample PBIX file