- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Difference in Ranks over different years
Hi everyone,
I'm trying to work on a Country Ranking Report and to write a DAX Measure which would help me to see what the Difference in Rank of a Country is from it's rank in the previous year.
Sample Data
Country | Rank | Year |
A | 1 | 2017 |
B | 2 | 2017 |
C | 3 | 2017 |
B | 1 | 2018 |
A | 2 | 2018 |
C | 3 | 2018 |
B | 1 | 2019 |
C | 2 | 2019 |
A | 3 | 2019 |
Wanted Result
slicer selection : year 2018
Country | Rank | Year | Difference From Prev Year |
B | 1 | 2018 | +1 |
A | 2 | 2018 | -1 |
C | 3 | 2018 | 0 |
Also there are some countries in the dataset that are ranked in a particular year but maybe not in the year before or next so hence there's no record of that country alongside those years after or before. If possible would there be a way to show that Country's Rank difference as [N.R] in that particular case.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Please check the below picture and the attached pbix file.
Rank measure: =
IF( HASONEVALUE(country_dim[Country]), MAX(rank_fct[Rank]))
Diff from prev year measure: =
VAR _prevyear =
MAX ( year_dim[Year] ) - 1
VAR _prevyearrank =
CALCULATE ( [Rank measure:], year_dim[Year] = _prevyear )
RETURN
IF (
NOT ISBLANK ( [Rank measure:] ) && NOT ISBLANK ( _prevyearrank ),
SWITCH (
TRUE (),
[Rank measure:] > _prevyearrank,
"+ " & [Rank measure:] - _prevyearrank,
[Rank measure:] < _prevyearrank,
"- " & _prevyearrank - [Rank measure:],
"0"
),
"NR"
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It worked! Thank you so much.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Please check the below picture and the attached pbix file.
Rank measure: =
IF( HASONEVALUE(country_dim[Country]), MAX(rank_fct[Rank]))
Diff from prev year measure: =
VAR _prevyear =
MAX ( year_dim[Year] ) - 1
VAR _prevyearrank =
CALCULATE ( [Rank measure:], year_dim[Year] = _prevyear )
RETURN
IF (
NOT ISBLANK ( [Rank measure:] ) && NOT ISBLANK ( _prevyearrank ),
SWITCH (
TRUE (),
[Rank measure:] > _prevyearrank,
"+ " & [Rank measure:] - _prevyearrank,
[Rank measure:] < _prevyearrank,
"- " & _prevyearrank - [Rank measure:],
"0"
),
"NR"
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
08-16-2024 02:52 AM | |||
07-16-2024 12:28 AM | |||
10-07-2024 05:11 AM | |||
10-12-2024 12:45 PM | |||
09-12-2024 08:26 AM |
User | Count |
---|---|
13 | |
12 | |
10 | |
7 | |
7 |
User | Count |
---|---|
18 | |
14 | |
11 | |
11 | |
10 |