Re: Problem comparing Budget and Actual tables

Anonymous · ‎01-08-2018

Hi guys!

I'm currently working on a report where we compare the budget vs reality of different accounts over a myriad of projects.

I only have 2 tables with all of the information required (Budget, Projects). I have created another table (Project) to join the two dimension tables because neither of them have unique values.

One of the things that I want to be able to do is filter the two amounts by project, client, or account. I've seen that most cases where you compare the budget vs the actual amount you do it by date however I only have dates for the actual amount not for the budget 😞 .

However, when I try to compare the information I get the error where it only shows the total of one of the tables (this happens where it's filtered or not).

In the picture above "G0", "U0", and "U09", are some of the different projects and "Almacen", "Almacen de PT Fabricadas Ubicación", "Mano de Obra", etc are some of the possible accounts.

I have also tried creating a new table of account and playing with the relationships between the tables (tried essentially every combination I can think of but it just doesn't seem to work. I've also tried merging the tables but that was a no go. I researched some other cases that were similar to mine on the Power BI forums and even in a book I found of Power BI but I haven't been able to crack it yet. I would truly appreciate any guidance! I really need this report but I'm fairly new to Power BI and am struggling a bit. I would appreciate any help you can offer me.

Thanks in advance!

mattbrice · ‎01-08-2018

It looks like the values for possible accounts you have in visual are from 'Actual' table, and P.Creb and P.Deb are aggregations from 'Budget' table. the reason you are getting total value is becuase even though bi-directional filtering is being applied to model, 'Accounts' are more granular than 'Projects' so filter not being propagated from Budget -> Actual.

Couple of ways to go, you can either append/merge the two tables together (though you said this didnt' work), or you change your 'Project' table to have a composite key that is concatenation of Project & Account. Create the same Project & Account fields in both 'Budget' & 'Actual' tables, link them all together, and it should work.

If you still can't get it to work, you can post a link to a sample pbix file and someone can help.

Anonymous · ‎01-10-2018

Hey Matt,

Thanks for the swift reply.

I actually already tried that too. Unfortunately it doesn't completely work. When I put them in the same table it no longer shows the total amount, however, it doesn't show all of the accounts in that project.