CODEWITHSUNDEEP
powerbidaxmeasuretop-n
NerdFlanders
NerdFlanders

Reputation: 151

DAX Measure Top N with others in Power BI

I'm developing a report in Power BI where i need to show the Top N Countries by the value of some Produkts they produce.

I already managed it to calculate the Top N countries which are variable, but I have no idea how to sum the "Others" in there.
So i need e.g. the Top 5 plus "Others" in the stacked Bar Chart.
Unfortunately I can't add a measure in the legend field, so I need a workaround for that.

Upvotes: 2

Views: 3940

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

You have to add an "Other" country in your countries table, since you cannot add a country or group of countries dynamically.

First you have to create a rank measure.

Sales Rank=RANKX(ALL(Data[Country]),[Sales])

Use the rank measure to determine how to sum the measure you want to show.

Top3:=IF ([Sales Rank] <= 3,[Total],
  IF(HASONEVALUE(Data[Country]),
    IF(VALUES(Data[Country]) = "Others",
       SUMX ( FILTER ( ALL ( Data[Country] ), [Sales Rank] > 3 ), [Total] )
    ) 
  ) 
)

Check this answer I posted in other question.

Let me know if this helps.

Upvotes: 4

Related Questions