Hi, Apologies for the delay in replying - but its take me this long to create some sample data and feel confident that I've asked the right question.
Background: I will have two tables. the first will contain a list of tasks (rows) and all possible resource groups (columns - called Booking Groups). For each booking group I have a certain amount of available resource (hours) which I want to assign to the tasks based on their expected effort (a percentage). The issue described above is that while I made individual entries work I couldn't get the Grand Total Rows to work. I've been going mad trying to identify the cause). I think the following comments now describe at least part of the problem.
Data Source. This is a small sample of how the task table might look which is how users like to enter their assignments:
Excel 2016 (Windows) 32 bit
|
|
| | |
|---|
| Data Table of Tasks against Booking Groups | | | |
| | Booking Groups | | |
| Task | A | C | D |
| Task T1 | 20% | |
|
| Task T2 | | 5% |
|
| Task G3 | 80% | |
|
| Task G4 | | 11% | 10% |
| Task Something | | 84% | 60% |
<tbody>
</tbody>
When 'Unpivoted' using PowerQuery this becomes:
Excel 2016 (Windows) 32 bit
|
| | |
|---|
| TASK | Booking Group | Expected Effort |
| Task T1 | A | 20% |
| Task T1 | D | 10% |
| Task T2 | C | 5% |
| Task T2 | D | 10% |
| Task G3 | A | 80% |
| Task G3 | D | 10% |
| Task G4 | C | 11% |
| Task G4 | D | 10% |
| Task Something | C | 84% |
| Task Something | D | 60% |
<tbody>
</tbody>
Then, using PowerPivot, I created the following table. One set of column entries is an implicit calculation 'Sum of Expected Effort' the other is a measure defined as
'SummedEffort'=sum('ExpectedEffort by Booking Group'[Expected Effort]) The two results are clearly different when I expected them to be the same (I'm reading Rob Collie's book on PowerBI etc which discusses implicit and explicit functions). The correct entry I believe are the ones in the 'Sum of Expected Effort' columns. I think it is this difference which is causing my problems as I need to multiply SummedEffort by the available resource - if SummedEffort were the same as the other column all would be well in the world.
Excel 2016 (Windows) 32 bit
|
| | | | | | | | |
|---|
| | Column Labels | | | | | | | |
| | A | | C | | D | | Total Sum of Expected Effort | Total SummedEffort |
| Row Labels | Sum of Expected Effort | SummedEffort | Sum of Expected Effort | SummedEffort | Sum of Expected Effort | SummedEffort | | |
| Task G3 | 80% | 300% | | | 10% | 300% | 90% | 300% |
| Task G4 | | | 11% | 300% | 10% | 300% | 21% | 300% |
| Task Something | | | 84% | 300% | 60% | 300% | 144% | 300% |
| Task T1 | 20% | 300% | | | 10% | 300% | 30% | 300% |
| Task T2 | | | 5% | 300% | 10% | 300% | 15% | 300% |
| Grand Total | 100% | 300% | 100% | 300% | 100% | 300% | 300% | 300% |
<tbody>
</tbody>
I suspect the answer lies in the correct use of 'Calculate(...)', but I don't seem to be able to get it right. Grateful for any advice or guidance.
Regards
Peter
