Average

[fedeleu]

Hi!

I have a model with the next table (this is a small sample of a much larger base)

Name	Year	Month	Review	Count	Weight
Name1	2021	1	A1	4	10
Name1	2021	1	A1	1	60
Name1	2021	3	A2	5	30
Name1	2021	9	A3	2	5
Name2	2021	8	B1	1	10
Name2	2021	10	B2	1	10

For each "Name", I must first group by "Review" and calculate the difference between 100 and the sum of its "Weight".

So, I create two measures

aux = SUMX([Table],Table[Count]*Table[Weight])

aux2 =

VAR CUENTA=100-[aux]

VAR CUENTA2=IF(CUENTA<0,0,CUENTA)

RETURN

IF([aux]<1,BLANK(),CUENTA2)

I get the following results, which were as expected

Name	Review	aux2
Name1	A1	0
Name1	A2	0
Name1	A3	90
Name2	B1	90
Name2	B2	90

OK, now is when I have a problem.

For each "Name", I must calculate the average of "aux2"

I created the following measure, but I'm not getting the expected results.

avg= AVERAGEX(NC_Peso_KPI,[aux2])

Name	avg	avg_expected
Name1	47,5	30
Name2	90	90

Can someone help me find the error?

Regards,

Federico.

[fedeleu]

Thanks for your help @AlexisOlson,
Can I bother you with another question?
I have 2 date slicers (one for month and one for year), both single-select.
Since the calculation of "avg" I must do it 12 months back, I adapted the measure as follows (there is a column "Date" that I did not name before)

Table [Date] is linked with Calendar [Date]

avg = CALCULATE(AVERAGEX (SUMMARIZE ( Table, Table[Name], Table[Review],Table[Date] ), [aux2] ),DATESBETWEEN(Calendar[Date],NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(Calendar[Date]))),LASTDATE(Calendar[Date])))

But doesnt work.

I get, at least in a small sample that I made, the same values ​​as without having made the change to your formula.

Any suggestion?

Thanks you

[AlexisOlson]

I can't quite tell what's going on from this description. You may want to create a new post with more detail and, ideally, link to a sample file others can follow along with.

[AlexisOlson · Accepted solution]

The reason it's giving this is that you are averaging at the table row granularity. That is, averaging these values:

To fix this, you need to average over the appropriate granularity:

avg = AVERAGEX ( SUMMARIZE ( Peso, Peso[Name], Peso[Review] ), [aux2] )