DAX - calculate the total based on average country value and per year

[Goldfinch]

Hi Guys,

For a project, I'm trying to show the total amount of sales. This total is dependent on the selected years (e.g. 2018 and 2019) and is always made for all countries in one box showing 1 total number. I've had some problems with getting the two criteria to work being (i) Year and (ii) Country code.

The amount need to be summed based on the years selected and all countries. Sometimes a country code will appear multiple times for 1 year because multiple entities are located in the same country but the sales amount is on country level so appears 3 times when you have for example 3 entities - see BE where you have 3x the country total sale amount.

I was tried making an average of the amounts based on country code and then using and SUMX and hasonevalue to get the total amount. But the hasonevalue only takes 1 criteria and does not take into account each year selected but delivers a total for all years - same problem if I reverse it.

Does anyone have any idea on how to tackle this?

Var_Year	Var_Country code	Sales amount
2018	BE	2.000
2018	BE	2.000
2018	BE	2.000
2019	BE	4.000
2019	BE	4.000
2019	BE	4.000
2018	FR	500
2019	FR	7.000
2018	GB	1.000
2019	GB	3.000

[daXtreme · Accepted solution]

[Total] =
    SUMX(
        DISTINCT( T ),
        T[Sales Amount]
    )