Rank by Country and Year

[mbraun]

Once again probably something very easy but I have hit a road block. See attached .pbix. I have 1 table. called WorldHappiness. There are multiple countries in this and multiple years 2015-2020. I believe that there is some combination of ALL and FILTER to achieve this. I am just stuckWorld Happiness.pbix (129.7 KB)

GOAL:
I want to create a ranking on the [Happiness Score] by Year.

Screen Shot of Data:

[Rajesh]

Hi @mbraun

Create calculate column.

World Happiness.pbix (132.0 KB)

[IlgarZarbaliyev]

Hi there,

First of all, I’d like to offer you to watch Sam’s following video on this topic:

RANKX Considerations - Power BI & DAX Formula Concepts

I would solve this issue in the following way:

1 - In this case, I wrote two measures: Total Happiness & Country Happiness Measures

Total Happiness Score = SUM(WorldHappiness[Happiness Score])
&
Country Happiness Ranking = RANKX(All(WorldHappiness[Country]), [Total Happiness Score],DESC)

Then, On canvas, I put slicer based upon years.
As you may see from the table attached, any selection year will cause ranking countries based upon their happiness measure.

2. In the second case, I made another measure using rankx, crossjoin and all DAX Functions for year and countries.
   Country&Year Ranking = RANKX(CROSSJOIN(ALL(WorldHappiness[Country]), All(WorldHappiness[Year])),[Total Happiness Score],DESC)

File attached.

I hope it can help you.

P.S. I changed year format to text one.
Forum_Zarbaliyev_World Happiness.pbix (103.3 KB)

[Rajesh]

@mbraun

Using Measure. If you want you can create multiple measures for each year.

World Happiness.pbix (132.5 KB)

[mbraun]

Thank you all for this! Much appreciated.