Separate Budget v Actual Tables to compare

[Anonymous]

Hi All

i have separate budget v actuals table

well actually i have an budget excel table being the place where managers enter in a budget for 4 categories for each month. this really represents a target goal

in another table is the actual output of the data they are targetting against. via the tool Azure DevOps 
the 4 categories are identified as 'Work Type Custom field'
i am wanting to calculate the total closed effort for each category based on a closed date timstamp.
represented as a % of the overall (which i can do quite easily)
the fields in this table are

- Closed Date (grouped by month)
- Work Type field which is the same category field in above screenshot

- Team

- Effort

a 3rd table helps roll up the 2 tables to corresponding groups/managers via team field/column

as well as a 4th table to help join all dates into a nice format.

i can separate calculate or represent each table but cannot seem to get it into a singular table

as per an eample below

where i fail, is that i can get either budget v actual with correct numbers in a matrix table. but not both
as per below, with budget i am getting the 100% per category rather than the value in the first table

eventually then i wish to have a measure to compare the 2 values per category

any help would be appreciated
thanks

[MFelix]

Hi @Anonymous ,

To what I can understand from your model you have several tables, but you do not explain the relationship between them. Looking at the details you gave you need to have a dimension table for the dates, work type and teams that then will relate your actual and your budget table.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.