Forum Discussion
Help aggregating data from indirect table
So I've solved my problem by normalizing some of my data further, but mainly by creating a measure using crossfilter which amounts to:
In my WorkStreamCapacities table
Allocation = CALCULATE(SUM(Tasks[Estimate]), CROSSFILTER(Tasks[WorkStreamId],WorkStream[id], both))
Now I can show a person's estimated allocation against their available capacity sliced by work stream.
Sample data please. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
workItems.pbix
Thanks Greg_Deckler,
I've included a link to a pbix with some dummy data that hopefully highlights the scenario.
At a high level, I effective just want to be able to display the amount of capacity a person has in a given work stream against the total hour of tasks assigned to them for that work stream.
Ideally users can use a slicer to target the work stream they care about.
The main issue Im facing is that total task time and total capacity are indirectly related to one another through the work stream table.
I believe I should be able to lean on measures here, but rearanging the data might also be appropritate. Just looking for some guidance.
In my example file Im also running into an issue where the slicer on the common workStream table doesn't seem to be affecting the tasks table, but not sure whats going on there. This answer https://community.powerbi.com/t5/Desktop/One-slicer-for-two-tables-M-M/td-p/523095 from someone else seems to be the exact relationship I have so I would expect the total task estimates to reflect this slice, but it always seems to display as the grand total.