Need help on DAX calculation using distinct value from one column

[analyticspbi87]

Right now, I have data structured in a way where a value is repeated several times for each name.

However, for a calculation, I only need it the one time. My current calculation works when PowerBI is filtering for each name, but for consolidation purposes it is wrong.

Name	Group	Type	Year	Value	EBIT	REV	Multiplier
An	A	Apples	1	1,005.00	10000	2000	1.03
An	A	Apples	2	2,378.63	10 000	2000	1.0609
An	A	Apples	3	603.00	10000	2000	1.092727
An	A	Apples	4	1,237.26	10000	2000	1.12550881
Abc	A	Apples	1	585.00	3000	5000	1.03
Abc	A	Apples	2	1,219.86	3000	5000	1.0609
Abc	A	Apples	3	516.25	3000	5000	1.092727
Abc	A	Apples	4	1,122.66	3000	5000	1.12550881

What I would like to show is the following set for the data each line when filtered,

EBIT % = Value (for the yr) / EBIT (per property) x Multiplier (for the year)

	1	2	3	4
An	10%	25%	7%	14%
ABC	20%	43%	19%	42%

And when consolidated to show the following 

	1	2	3	4
TOTAL	13%	29%	9%	20%

Right now, my measure is the following :

DIVIDE(
SUM('Table'[Value]), AVERAGE('Table'[EBIT])
*SUM('Table'[Multiplier])

However, the average for EBIT is wrong when I do the consolidated total.

Just wondering if there are way to go about it. TIA!

[analyticspbi87 · Accepted solution]

This definitely helped point me in the right direction.

I ended up switching the formula to the following and it looks like it's working

 SUMX(DISTINCT('Table'[Name]), CALCULATE(MAX('Table'[EBIT])))

Thank you so much!!!😊

[freginier]

Hey there!

using AVERAGE which might not be the correct way to handle EBIT because it can be affected by row duplication. This leads to incorrect consolidated totals.

try using this:

EBIT % =
DIVIDE(
SUM('Table'[Value]),
SUMX(DISTINCT('Table'[Year]), CALCULATE(SUM('Table'[EBIT])))
) * SUM('Table'[Multiplier])

Hope this helps!

😁😁