Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Friends
I am seeking your valuable assistance.
I am able to design a matrix in powerbi, with totals showing properly at required hierarchy levels. My problem is that for some columns, I need to do a SUM in the Total row, while for others I need to calulate the average instead. Examples, for Revenue it makes sense to get the Total while for % Profit, the 'Total' should show the AVERAGE of the rows.
I attach a sample Test data with the desired output of the matrix. In the sample data, when column [Unit of Measure] = Units, I need to sum, when the column = %, then I need to average.
Thank you for your help.
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 | % | |
Desired output in Matrix | |||||
Revenue | % Profit | No of Complaints | % Leavers | ||
Consolidated | 67000 | 30 | 12 | 5 | |
CPYA | 24-Jan | 20000 | 60 | 3 | 6 |
24-Feb | 10000 | 20 | 2 | 8 | |
Total/Average | 30000 | 40 | 5 | 7 | |
CPYB | 24-Jan | 25000 | 30 | 6 | 4 |
24-Feb | 12000 | 10 | 1 | 2 | |
Total/Average | 37000 | 20 | 7 | 3 | |
Grand Total | 67000 | 30 | 12 | 5 |