Forum Discussion
Plot residual value
Hi
I have a data source like this,
| PN | ID | Date | Val |
| PN_1 | User_1 | wk_1 | 0.2 |
| PN_1 | User_1 | wk_2 | 0.4 |
| PN_1 | User_1 | wk_3 | 0.6 |
| PN_1 | User_2 | wk_2 | 0.4 |
| PN_1 | User_2 | wk_3 | 0.4 |
| PN_2 | User_1 | wk_1 | 0.2 |
| PN_2 | User_2 | wk_1 | 0.2 |
| PN_2 | User_2 | wk_2 | 0.2 |
| PN_2 | User_3 | wk_2 | 0.4 |
| PN_2 | User_3 | wk_3 | 0.4 |
The report has a date slicer and will have a pie chart to show the PN against Val values. When a different ID is selected, the pie chart will display the PN and the Val. As the Val value summation for each user may not always be 1 in each week, I want to have the "residual" value to be plotted in the pie or created in the table.
In my mind, I expect the final data to be like the table below, those rows of in blue are created by some tricks in Power BI.
| PN | ID | Date | Val |
| PN_1 | User_1 | wk_1 | 0.2 |
| PN_1 | User_1 | wk_2 | 0.4 |
| PN_1 | User_1 | wk_3 | 0.6 |
| PN_1 | User_2 | wk_2 | 0.4 |
| PN_1 | User_2 | wk_3 | 0.4 |
| PN_2 | User_1 | wk_1 | 0.2 |
| PN_2 | User_2 | wk_1 | 0.2 |
| PN_2 | User_2 | wk_2 | 0.2 |
| PN_2 | User_3 | wk_2 | 0.4 |
| PN_2 | User_3 | wk_3 | 0.4 |
| Res | User_1 | wk_1 | 0.6 |
| Res | User_1 | wk_2 | 0.6 |
| Res | User_1 | wk_3 | 0.8 |
| Res | User_2 | wk_1 | 0.8 |
| Res | User_2 | wk_2 | 0.4 |
| Res | User_2 | wk_3 | 0.6 |
| Res | User_3 | wk_1 | 1 |
| Res | User_3 | wk_2 | 0.6 |
| Res | User_3 | wk_3 | 0.6 |
I can calculate the residual value but I cannot figure out a way to make this residual value to be included to the table and associate with Res which wasn't inputted in the original data. I am not sure this is something can be done by the visualisation or DAX, hence, any guidance and tip will be much appreciated.
I think I would generate these "res" records using PowerQuery.
You can do this by
- Right click on your existing query and choose Reference to create a new query that references the current one.
- Then remove the PN column and group by date and id and sum the Val column
- Create a new Val column that is 1 - Val
- Then Add "Res" as a constant value for a new PN column
- Then append the existing query to this new one
- Finally right click on the original query and disable the load
You can find a working example of the above steps in the attached pbix file
2 Replies
I think I would generate these "res" records using PowerQuery.
You can do this by
- Right click on your existing query and choose Reference to create a new query that references the current one.
- Then remove the PN column and group by date and id and sum the Val column
- Create a new Val column that is 1 - Val
- Then Add "Res" as a constant value for a new PN column
- Then append the existing query to this new one
- Finally right click on the original query and disable the load
You can find a working example of the above steps in the attached pbix file