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.
I have data that looks like this:
Element | Value | Product |
Iron | 4 | 1 |
Iron | 8 | 1 |
Sulphur | 3 | 1 |
Iron | 0 | 1 |
Sulphur | 3 | 1 |
Manganese | 6 | 1 |
I need to get the average of every element except one Element, then the average of that last element. Do some calculations and return the result... I have page filters that needs to be taken into account (on Product amongst others).
This is what i tried, but the result don't match my manual calculations.
SUM =
(CALCULATE(
AVERAGE('Analysis'[Value]),
FILTER('Analysis', 'Analysis'[Element] = "Iron")
) * 1.382)
+
(CALCULATE(
AVERAGE('Analysis'[Value]),
FILTER('Analysis', 'Analysis'[Element] <> "Iron")
) + 1.2)
Solved! Go to Solution.
I managed to find the solution.
SUMX(
GROUPBY(
'Analysis',[Element],
"Element_grp", MAXX(CURRENTGROUP(),[Element]),
"Value_avg", AVERAGEX(CURRENTGROUP(),[Value])
),
IF([Element_grp] = "Iron", [Value_avg] * 1.382, [Value_avg])
)+1.2
I can add in that the first half of the measure works as expected.
The second one seems to average all values from all categories, not for each category, and it results in a much lower number than expected.
@bingbadabom at once place you are multiplying bt 1.3 and at the second place you are adding 1.2 . That you need at line level or total level. The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.
What i was handed on a post-it note was the following:
(Iron * 1.382) + (Oxides) + 1.2
Where Iron is the average of all Iron values, and Oxides is the sum of the average value of all other elements.
The multiplying/adding is some adjustments that has to be done before presenting the result.
I managed to find the solution.
SUMX(
GROUPBY(
'Analysis',[Element],
"Element_grp", MAXX(CURRENTGROUP(),[Element]),
"Value_avg", AVERAGEX(CURRENTGROUP(),[Value])
),
IF([Element_grp] = "Iron", [Value_avg] * 1.382, [Value_avg])
)+1.2
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 |
---|---|
112 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |