Wrong average calculation

[wsspglobal]

Hi 

I created a matrix table for to calculate the average of my data by each category. Below is the montly expense data I have, classified by "abnormal" and "normal" expenses. I think the averages of "Abnormal" that the matrix calculated are odd because the averages I calculated in Excel for each year are 2018: $209.45, 2019: $700.80, 2020: $529.25. I used the same approach to calcualte the average monthly expense for each year for "Normal" in Excel and got the same result as the matrix. So I don't understand why there is a difference when calculating for "Abnormal". Also, I expect the total of all the averages should be the total monthly average of all 21 months listed here at $2,130.21. How is the matrix calculating the averages? Please help, thanks!

Year	Month	MonthNo	Abnormal	Monthly Total
2018	August	8	Abnormal	345.40
2018	August	8	Normal	1,212.56
2018	September	9	Abnormal	180.00
2018	September	9	Normal	1,300.74
2018	October	10	Abnormal	227.45
2018	October	10	Normal	1,729.37
2018	November	11	Normal	1,859.07
2018	December	12	Abnormal	294.40
2018	December	12	Normal	1,326.64
2019	January	1	Normal	1,120.02
2019	February	2	Abnormal	971.50
2019	February	2	Normal	924.73
2019	March	3	Abnormal	572.14
2019	March	3	Normal	1,907.97
2019	April	4	Abnormal	2,397.20
2019	April	4	Normal	2,294.14
2019	May	5	Abnormal	725.00
2019	May	5	Normal	1,946.88
2019	June	6	Abnormal	178.97
2019	June	6	Normal	1,744.59
2019	July	7	Abnormal	409.37
2019	July	7	Normal	1,761.31
2019	August	8	Abnormal	579.00
2019	August	8	Normal	1,672.41
2019	September	9	Abnormal	1,025.83
2019	September	9	Normal	1,750.29
2019	October	10	Abnormal	129.00
2019	October	10	Normal	1,517.06
2019	November	11	Abnormal	264.61
2019	November	11	Normal	2,028.08
2019	December	12	Abnormal	1,157.04
2019	December	12	Normal	1,440.11
2020	January	1	Abnormal	1,489.07
2020	January	1	Normal	1,525.59
2020	February	2	Abnormal	189.49
2020	February	2	Normal	1,648.56
2020	March	3	Abnormal	287.71
2020	March	3	Normal	2,197.89
2020	April	4	Abnormal	150.71
2020	April	4	Normal	252.58

[v-zhenbw-msft]

Hi @wsspglobal ,

We can use the following measure to calculate the total of each row and column. You can refer it.

average total = 
IF (
    ISINSCOPE ( 'Table'[Abnormal] ),
    SUMX (
        VALUES ( 'Table'[Year] ),
        CALCULATE ( AVERAGE ( 'Table'[Monthly Total] ) )
    ),
    SUMX (
        VALUES ( 'Table'[Abnormal] ),
        CALCULATE ( AVERAGE ( 'Table'[Monthly Total] ) )
    )
)

The result like this,

If it doesn’t meet your requirement, could you please show us the exact expected result based on the table that we have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

Do you have rows with zero values in the Excel file that are not shown in the data provided?

Let's look at (2018 Abnormal) data provided:-

180 + 227.45 + 294.4 + 345.4 = 1,047.25

There are 4 entries and average shown in the matrix is 261.81. (1047.25 divided by 4)

If we take the average you give from excel (209.45) and divide the total above (1047.25), we get a nice round 5. So it suggests there's another row somewhere.

I haven't looked at the other years but it might be similar.