cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LilyS Frequent Visitor
Frequent Visitor

Problem comparing Budget and Actual tables

Hi guys!

 

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

 

Relationships.JPG 

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 Smiley Sad .

 

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). 

 

Table.JPG

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!

3 REPLIES 3
mattbrice Senior Member
Senior Member

Re: Problem comparing Budget and Actual tables

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.

LilyS Frequent Visitor
Frequent Visitor

Re: Problem comparing Budget and Actual tables

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. 


Relationships Concatenated.JPG

 

Table Con.JPGBelow is the real amount of accounts for that project. 

 

Table Seperated.JPG

 

 I'll make a sample pbix file ASAP. 

 

Thanks for your help!

LilyS Frequent Visitor
Frequent Visitor

Re: Problem comparing Budget and Actual tables

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 50 members 1,056 guests
Please welcome our newest community members: