topic Re: LOD and measure totals in DAX Commands and Tips

LOD and measure totals

HelpImStuck — Fri, 05 Jan 2024 00:38:15 GMT

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!

Re: LOD and measure totals

some_bih — Sat, 06 Jan 2024 18:56:14 GMT

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],

Re: LOD and measure totals

HelpImStuck — Mon, 08 Jan 2024 13:27:57 GMT

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

Re: LOD and measure totals

some_bih — Mon, 08 Jan 2024 17:35:37 GMT

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

Re: LOD and measure totals

HelpImStuck — Mon, 08 Jan 2024 18:20:12 GMT

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

Re: LOD and measure totals

some_bih — Mon, 08 Jan 2024 18:28:11 GMT

Hi HelpImStuck sure.

It could be transformed into dimension using power query