Dear community,
I am fairly new in the PowerBi world and I am struggling with the following case:
I have a set of data like this in excel:
Table 1 - Data
| Workers | Average of KPI 1to4 | KPI1 | KPI2 | KPI3 | KPI4 |
| Worker 1 | 3,0% | 3,0% | | | |
| Worker 1 | 63,8% | 52,5% | 75,0% | | |
| Worker 1 | 45,8% | 37,5% | 25,0% | | 75,0% |
| Worker 1 | 10,0% | 10,0% | | | |
| Worker 2 | 50,0% | | | | 50,0% |
| Worker 2 | 56,3% | 50,0% | | | 62,5% |
| Worker 2 | 25,0% | 25,0% | | | |
In PowerBi I would like to get a consolidated view per worker like this:
Table 2 - Desired outcome
| Workers | Average of KPI 1to4 | Average of KP1 | Average of KP2 | Average of KP3 | Average of KP4 |
| Worker 1 | 50,25% | 25,75% | 50% | | 75% |
| Worker 2 | 46,87% | 37,5% | | | 56,25% |
The columns "Average of KPI1, KPI2, KPI3 and KPI4" are no problem. By just selecting the data "KPI1" and then select that it should be shown as an average the table is showing the right values.
The issue is if I add to the table the data "Average of KPI 1to4" and then select average, the average shown in the column "Average KPI 1 to 4" is the average of the values of Table 1 of the rows for each worker "Average KPI 1 to 4" (Data) (e.g., for worker1 average of 3, 63.8, 45.8, 10) instead of being the average of the values "average KPI1", "average KPI2", "average KPI3", "average KPI4" (e.g., 25.75, 50, 75) resulting from the consolidation. Empty cells should not be considered for averages.
So instead of getting the desired values shown in Table 2 I am getting the following values:
Table 3 - Current wrong outcome
| Workers | Average of KPI 1to4 | Average of KP1 | Average of KP2 | Average of KP3 | Average of KP4 |
| Worker 1 | 30,65% | 25,75% | 50% | | 75% |
| Worker 2 | 43,76% | 37,5% | | | 56,25% |
Does anyone have an idea how this can be solved?
Thank you for your help!
Best regards,
Okimrats
