Help with Dynamic Pie Chart DAX

[buddycho]

I have a table called "2017-2023 FDI" with the columns "Year" "NE" "County" "FDI Country" "CapEx" "Jobs" "SSP Industry". In the NE column the cells have either "N" to represent new projects and "E" to represents expanding projects. A count is taken of the entries in the "NE" column to determine the Total Projects as well as a count of "N" in the "NE" column can be taken to determine the number of new projects and a count of "E" in the "NE" column can be taken to determine the number of expanding projects. Cells in the "FDI Country" column list the country where the FDI originated. I want to have a pie chart where the value is "CapEx" and the Legend is the Top 5 FDI Countries by total "CapEx" with the remainder FDI countries grouped under the single category other. I want a second pie chart where the value is "Jobs" and the Legend is the same as above. I want a third pie chart where the value is project count (Count of "NE") and the Legend is the same as above. This means that each pie chart should have 6 pieces

I keep getting an error that the syntax for "Rank" is incorrect, I am not sure how to proceed. I tried using all measures instead of having a calculated column that groups countries into "Top 5" or "Other" but realized I could not put a measure in the Legend field. The new table I created is called "Metrics"

           

[johnt75 · Accepted solution]

I think you could use the principles described in https://www.sqlbi.com/articles/filtering-the-top-products-alongside-the-other-products-in-power-bi/