Previous 2 year values for current top 7 countries and others

DAXUser — Fri, 17 May 2024 03:27:24 GMT

I want to show the sales of TOP 7 countries for the selected year and the remaining sales I need to add up the country and show it as "Other" in Country Name column. After that, Based on current top 7 countries names i need to compare with previous last two years same country sales amount.

Calendar	Country Name	Total
2024	Qatar	18.98
2024	Australia	15.28
2024	Malaysia	12.2
2024	UK	9.64
2024	United States	4.56
2024	Netherlands	3.32
2024	Canada	1.28
2024	Others	17.4

Expected Ouput: Comparing current top 7 countries with previous year

Calendar	Country Name	Total
2024	Qatar	18.98
2024	Australia	15.28
2024	Malaysia	12.2
2024	UK	9.64
2024	United States	4.56
2024	Netherlands	3.32
2024	Canada	1.28
2024	Others	17.4
2023	Qatar	20.98
2023	Australia	17.28
2023	Malaysia	16.2
2023	UK	10.64
2023	United States	9.56
2023	Netherlands	8.32
2023	Canada	6.28
2023	Others	19.4
2022	Qatar	22.98
2022	Australia	17.98
2022	Malaysia	13.2
2022	UK	11.64
2022	United States	10.56
2022	Netherlands	9.32
2022	Canada	7.28
2022	Others	19.76

Measure used:

Total Sales =

VAR _start= Year(MAX ( 'Calendar'[Date] ))

VAR _end = _start - 2

CALCULATE (

ROUND( CALCULATE(Sales amount),2),

ALL ( 'Calendar' ),

FILTER (

'DATE',

year('DATE'[DATE_TIMESTAMP_DTM]) <= _start

&& year('DATE'[DATE_TIMESTAMP_DTM]) >= _end

)

This measure i used to calculate top 7 and others, and also to display last 2 year sales value comparsion with current year sales values of the top 7 countries

Top7=
VAR Top_N = 7

VAR TOPNCOUNTRY = TOPN(Top_N,
ALL('COUNTRY'),
[Total Sales]
)

VAR ALLCOUNTRY = CALCULATE( [Total Sales],ALLSELECTED('COUNTRY'))

VAR OTHERCOUNTRY = ALLCOUNTRY - CALCULATE( [Total Sales,TOPNCOUNTRY)

VAR TOPNCOUNTRYDIS = CALCULATE( [Total Sales],KEEPFILTERS(TOPNCOUNTRY))

VAR CURRENTPCOUNTRY = SELECTEDVALUE('INTENSITY COUNTRY'[COUNTRY])

RETURN
IF( CURRENTPCOUNTRY = "Others",
OTHERCOUNTRY,
TOPNCOUNTRYDIS
)

Problem:
Previous year values for current top 7 countries name is not happening instead each year it displays top 7 and others

Re: Previous 2 year values for current top 7 countries and others

lbendlin — Fri, 17 May 2024 13:10:03 GMT

As long as you are fully aware that the Top 7 of the current year may not even exist in the prior year (which makes the comparison questionable) this is a straight forward process.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

topic Re: Previous 2 year values for current top 7 countries and others in DAX Commands and Tips

Previous 2 year values for current top 7 countries and others

Re: Previous 2 year values for current top 7 countries and others