Pie Chart of Top 8 with Other Filtered by Product and Date

[cwilson]

I'm trying to build a pie chart by filtering a larger table that gives me sum of a selected product ranked to the top 8 countries with the  remainder in "other." The full table includes all countries, about 20 different products going back monthly about 20 years and I am trying to create a pie chart of export volumes totaled by filtered products for a selected month or period of time. Currently I'm manually grouping them each month but is there a way to dynamically rank each country based on the filters applied and show the top 8 plus the rest as Other?

[v-easonf-msft · Accepted solution]

Hi， @cwilson 

Please try follow steps:

1. build a calculated table like:

Pseudo Table = 
UNION(
    DISTINCT('Table'[Country]),
    DATATABLE("Country",STRING,{{"Others"}})
)

2. Then try measure like:

Top 2 Sum Sales = 
VAR TopNSelected = 2
VAR TopCountryTable = 
    TOPN(
        TopNSelected,
        ALLSELECTED('Pseudo Table'),
        [Value]
    )
VAR TopCountrySales =
    CALCULATE(
         [Value],
        KEEPFILTERS( TopCountryTable  )
    )
VAR OtherSales = 
    CALCULATE(
         [Value],
        ALLSELECTED('Pseudo Table')
    ) - 
    CALCULATE(
         [Value],
        TopCountryTable 
    )
VAR CurrentProd = SELECTEDVALUE('Pseudo Table'[Country])
RETURN
    IF(
        CurrentProd <> "Others",
        TopCountrySales,
        OtherSales
    )

Please refer to  the following tutorial for more details.

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

Best Regards,
Community Support Team _ Eason