Forum Discussion
Solved
Help in matrix total
Hi all
Can you help me. I would like some columns to show the average, while others the sum. In the first column, I want the 'total' to appear as 7 ( ie average of 6 and 8 ), Whereas column No of Complaints should be the Sum, as it is now. How can I achieve so? I attacj my sample Excel sheet. There is a colum in the Excel cnamed Units, which determine if the aggregation is either Sum or Average. I added a clolum CFL in the pbix for the parent Company ( which holds CPYA and CPYB) to appear in the visual. I will also use Filter as below.Many thanks.
Sample Data Sheet.
| Company | Month | Metric | Value | Unit of Measure |
| CPYA | 24-Jan | Revenue | 20000 | Units |
| CPYA | 24-Jan | % Profit | 60 | % |
| CPYA | 24-Jan | No of Complaints | 3 | Units |
| CPYA | 24-Jan | % Leavers | 6 | % |
| CPYB | 24-Jan | Revenue | 25000 | Units |
| CPYB | 24-Jan | Percentage Profit | 30 | % |
| CPYB | 24-Jan | No of Complaints | 6 | Units |
| CPYB | 24-Jan | % Leavers | 4 | % |
| CPYA | 24-Feb | Revenue | 10000 | Units |
| CPYA | 24-Feb | % Profit | 20 | % |
| CPYA | 24-Feb | No of Complaints | 2 | Units |
| CPYA | 24-Feb | % Leavers | 8 | % |
| CPYB | 24-Feb | Revenue | 12000 | Units |
| CPYB | 24-Feb | % Profit | 10 | Percentage |
| CPYB | 24-Feb | No of Complaints | 1 | Units |
| CPYB | 24-Feb | % Leavers | 2 | % |
see attached. I cleaned up your sample data a bit.
