Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
I think I would generate these "res" records using PowerQuery.
You can do this by
You can find a working example of the above steps in the attached pbix file
I think I would generate these "res" records using PowerQuery.
You can do this by
You can find a working example of the above steps in the attached pbix file
Thanks, d_gosbell.
This exactly gives me what I need.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |