Sum of totals

[jelleschuurman]

Hi,

I have a very simple table containing 3 columns:

- Locatie

- Date

- Waarde (value)

I created a measure:

#waarde = sumX(SUMMARIZE('table 1','table 1'[Locatie], "naam",AVERAGE('table 1'[Waarde])),[naam])

I show the data in a Pivot:

I'm looking for the average of the orange selection. The average should be 22376 (calculated in Excel) but it shows 23916 (this is correct for the total column, but not for the total row in this case). How can I calculate the value i'm looking for?

Thank you.

[jelleschuurman]

@tamerj1 

It sort of works in this model 🙂 I have to divide the total by the amount of months (12 in this case) and then I get the expected result. all the other values are then incorrect, but i don't need them in this case.

I tried to do this in a datamodel with a seperate date table, but then I do not get the wanted result. I tink its because of filters. I tried to exclude them with All/Allexpect but I cannot get the right amount.

This table 1 is not connected directly to the date table, but it is via another related table.

Is sumx with summarize the correct way to calculate this? I also tried the groupby function and get the same results. 

[tamerj1]

@jelleschuurman 

Please share a screenshot of the data model with the relationships 

[jelleschuurman]

Hi @tamerj1 

Sorry for the late reply.

I looked at the datamodel and saw that I made a mistake in the model. I changed the model and the formula (without 'table 1'[datum].MONTH,) is working correct.

[tamerj1]

Hi @jelleschuurman 

the month column is from which table?

[jelleschuurman]

Hi @tamerj1 ,

The same table:

[tamerj1 · Accepted solution]

@jelleschuurman 

Not sure but worth a try

#waarde =
SUMX (
SUMMARIZE (
'table 1',
'table 1'[Locatie],
'table 1'[datum].MONTH,
"naam", AVERAGE ( 'table 1'[Waarde] )
),
[naam]
)