Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
[Total] =
SUMX(
DISTINCT( T ),
T[Sales Amount]
)
[Total] =
SUMX(
DISTINCT( T ),
T[Sales Amount]
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |