Forum Discussion
Average
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
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.
| Name | avg | avg_expected |
| Name1 | 47,5 | 30 |
| Name2 | 90 | 90 |
Can someone help me find the error?
Regards,
Federico.
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] )
3 Replies
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] )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
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.
