Forum Discussion
Matrix with both total and average
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!
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 _resultThe PBIX file is attached for reference.
Best Regards,
Gao
Community Support TeamIf 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
1 Reply
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 _resultThe PBIX file is attached for reference.
Best Regards,
Gao
Community Support TeamIf 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