Sum of Average of Category

[Anonymous]

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)

[Anonymous]

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.

[amitchandak]

@Anonymous 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.

[Anonymous]

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.

[Anonymous · Accepted 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