Forum Discussion
Using parameters to edit two different values of a single line and be able to view new grand total
I am working on a dashboard that has two parameters - a month paramter and a percentage paramter. The dashboard is designed to have the two parameters change the value of individuals encumbrance - encumbrance is a calculated column that takes the total avaliable times the percentage (example 10,000 times 10% = 1,000) then times the remaining months (4 months times 1,000 = 4,000 total encumbrance). The encumbrance of each person is added together to get the total remaining encumbrance. That total is compared to the remaining budget. In example, 4 people on a project, all have 4 months remaining and all have 1,000 encumbrance for a total of 16,000 total encumbrance (4 people times 4,000 of total encumbrance each). The 16,000 would be subtracted from the remaining budget, example of 20,000 remaining budget - 16,000 total encumbrance = 4,000 remaining budget. The paramters are there to see what the changes would be for individuals. Example if one person has their percentage changed to 20%. Their new total would be 10,000 times 20% = 2,000. Then their 2,000 would change the total encumbrance to 17,000 and remaining budget would be 3,000.
This first photo shows when I adjust the percentage using the paramter filter, it changes all of the employees percentage not just an individual. I would need to just adjust one person's months and percentage.
This second photo when I select a person I only see the totals inclduing the one person and not all the employees. I can adjust their percentage and months but I can't see how it changes the total budget. I am open to any and all ideas.
It would be like in excel where you can select individual cells to edit that would show a new total, but I cannot figure out how to view that in the dashboard. Thanks you for your help.
Hi zanderson45,
Based on our understanding of the issue, a possible approach is to use a disconnected Worker selector, apply the What-If parameters conditionally using SELECTEDVALUE(), and manage totals using ISINSCOPE() with SUMX() to reconstruct the full total. This ensures that only the selected worker changes while all other rows remain visible and the grand total remains accurate.
Please find attached the screenshots and a sample PBIX file for reference:
We hope that the information provided will assist in resolving the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.Thank you.
7 Replies
v-pnaroju-msftCommunity Support
Hi zanderson45,
Based on our understanding of the issue, a possible approach is to use a disconnected Worker selector, apply the What-If parameters conditionally using SELECTEDVALUE(), and manage totals using ISINSCOPE() with SUMX() to reconstruct the full total. This ensures that only the selected worker changes while all other rows remain visible and the grand total remains accurate.
Please find attached the screenshots and a sample PBIX file for reference:
We hope that the information provided will assist in resolving the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.Thank you.
Thank you v-pnaroju-msft, after some tinkering I was able to get it to work. Really appricate your time and help with this project.
- amitchandak
Super User
zanderson45 , Try like
Encumbrance =
VAR SelectedPerson = SELECTEDVALUE(Employee[EmployeeID])
RETURN
SUMX(Employee, Employee[Total Available]* IF( Employee[EmployeeID] = SelectedPerson, [Percentage Parameter Value], Employee[Original %])* IF(Employee[EmployeeID] = SelectedPerson, [Month Parameter Value], Employee[Original Months]))Total Encumbrance =
SUMX(ALL(Employee), [Encumbrance])Hello amitchandak, I tried using the DAX code you provided and it gave me the same totals I previously had while trying to alter one worker's numbers still did not work. I feel as though it may be a, issue with filtering and maybe using a different table than a matrix table but I cannot figure out how to correct that.
- danextian
Hi zanderson45
It would be easier for us to provide a working solution had you provided a sanitized (condfidential data removed) of your pbix and from the same, your expected result. You may post a link to a file stored in the cloud.
- v-pnaroju-msft
Thankyou, amitchandak and danextian , for your responses.
Hi zanderson45,
Thank you for the update.
Based on my understanding, the issue arises because selecting an employee applies a filter to the entire Employee table. When this occurs, Power BI recalculates totals only for the selected row. To maintain the grand total while modifying only one employee, we must explicitly remove that filter context within the measure.
Please find attached a screenshot and a sample PBIX file for reference:
We hope the information provided will assist in resolving the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.Thank you.
Hello v-pnaroju-msft ,
Company policy will not allow me to download the example file, is there a way you are able to screenshot other parts of the file?
In the screenshot you provided it is like the second screenshot that I provided where I am able to edit an individuals percent and months but that change is not compared to the other employees i.e. in your example the other employees’ figures are not visible to see the impact of the change to Alice’s months and percentage. I attached another screenshot that I edited to show what the goal of the dashboard is. The yellow box shows only c had their distribution percentage and months remaining adjusted while a and b remained the same.
