LOD and measure totals

[HelpImStuck]

I am stuck trying to maintain the same measure total when removing a column from a table visual. Here is my current measure, which gives the correct result and the correct total:

BD SUM =
VAR A =
SUMMARIZE (
Sheet1,
Sheet1[brand],
"Avg",
(
AVERAGE ( Sheet1[income] ) * Income[Income Value]
)
+ (
AVERAGE ( Sheet1[education] ) * Education[Education Value]
)
+ (
AVERAGE ( Sheet1[age] ) * Age[Age Value]
)
+ (
AVERAGE ( Sheet1[ethnicity] ) * Ethnicity[Ethnicity Value]
)
+ (
AVERAGE ( Sheet1[house] ) * 'Household Size'[Household Size Value]
)
+ (
AVERAGE ( Sheet1[marital] ) * 'Marital Status'[Marital Status Value]
)
+ (
AVERAGE ( Sheet1[children] ) * 'Presence of Children'[Presence of Children Value]
)
)
RETURN
SUMX (
A,
[Avg]
)

When adding this measure to a table visual, it generates this result:

id	brand	BD SUM
9007900349	A	2.45
9007900349	B	1.63
9007900349	C	2.4
9007900349	D	2.5
9007900349	E	1.88

The total of all 5 values is 10.86 which is correct, however, when I remove the 'brand' column from the table visual, it gives the wrong total. Any ideas how I can produce this result?

id	BD SUM
9007900349	10.86

I have tried every iteration of ALL, ALLEXCEPT, and VALUES I can think of but nothing seems to work. Appreciate the help!

[some_bih]

Hi @HelpImStuck sure. 

It could be transformed into dimension using power query

[some_bih]

Hi @HelpImStuck your definition for measure include brand, this is reason you "have" to have brand .Rewrite measure, try directly without variable part.

Sheet1[brand],

[HelpImStuck]

Hi @some_bih, the measure needs to calculate the sum of the averages by brand, which in this case is 10.86, but the visual needs to display that by ID. In my example there are 5 brands under ID 9007900349, and the the sum of the averages on those 5 brands is 10.86, so in a bar chart with ID as the y-axis, ID 9007900349 needs to have the value of 10.86. For some reason this does not calculate correctly unless I also include brand

[some_bih]

Hi @HelpImStuck did you try to use dimension table not fact table?

[HelpImStuck]

@some_bihthis is all one table, provided in a single CSV file