Plot residual value

[Anonymous]

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.

[d_gosbell · Accepted solution]

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

[Anonymous]

Thanks, d_gosbell.

This exactly gives me what I need.