Matrix with both total and average

[ruxandraalina]

Hello!

I need to create a matrix that shows monthly values (a count of tickets) by category, totals by category and averages by category, as below.

Months are to be selected from a slicer, so this is only an example. 

Category	January	February	March	Total	Average
A	5	8	2	15	5.00
B	6	5	6	17	5.67
C	3	6	8	17	5.67
D	12	13	7	32	10.67
E	15	4	5	24	8.00

I've tried using the switch formula and it does work for monthly values, but i can't get the total and the average. 

If anyone could help, that would be much appreciated!

Thank you!

[Anonymous · Accepted solution]

Hi @ruxandraalina ，

In this case, there are two values in the matrix, one calculated as the sum and one sum calculated as the average. Each value is pivoted, so that two values appear in each column of the matrix.

My idea is to hide the unwanted values, like this.

Here the Total column I am turning on the column subtotal and the average is adding the measure to the value.

Average = 
VAR _count_c = COUNT('Table'[Category])
VAR _count_m =DISTINCTCOUNT('Calendar'[Month])
VAR _result = DIVIDE(_count_c,_count_m)
RETURN
_result

The PBIX file is attached for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data