Matrix - replace Total per row with Average

[Anonymous]

A matrix typically will list totals at collumn and row level. In order to better analyse monthly variances, I would like to compare the lastest month's numbers with the YTD monthly average. So I basically would like to see an "Average" column rather than a "Total" column as per below. The column totals in the bottom row can stay. Is this possible?

[Anonymous]

HI @Anonymous,

Yes, it is possible. You can add if statement to check total level and write a specific formula for calculation of total level, then use condition to replace calculation on total level with that formula.

If you are confused on coding formula, please share some sample data or pbi file that we can test and coding formula on it.

Regards,

Xiaoxin Sheng

[Chihiro]

There are many ways to do it.

1. Calculate measures separately for each month, and also have measure for average. Then add each into Values field in sequence. Turn off Total columns.

2. Use two separate visual, one for monthly values, and another for average.

3. Use custom visual (R-Visual to create matrix on the fly, or one from the store, that suites your need).

Total column isn't able to do average calculation as far as I know.