Linking budgeted data to actual

[Jurre_B]

Hi all,

In order to create a functioning dashboard for my department, I am looking to link my actual revenue to the budgeted revenue, so that I can give an overview of whether we reached our budget or how much we were above/below it.

I currently have the budgeted and actual revenue seperated per month, with each day being a seperate column and each row being a different source of revenue.

Whenever I try to create a relationship between the actual revenue of e.g January, and the budgeted revenue of January, it gives me the following error:

Though the first column is the same (they both give the source of revenue), the data in the other columns (each day of the month), is different.

I am the first one in my office working with Power BI, so I am definitely still a beginner.

Thank you in advance!

[v-jiascu-msft]

Hi @Jurre_B,

Could you please mark the proper answer if it's convenient for you? That will be a help to others.

Best Regards!
Dale

[mattbrice]

Create another table with the unique revenue source values and use it to link the two tables together.  Put this new table column in the visuals (on rows), and then put in measures for actual v budget.

[Jurre_B]

Thanks for your reply.

I created a new table with only the revenue sources in it, but when trying to link my actual revenue (from e.g. January), to the revenue streams, it still gives the same error message as I posted in my original message.
This makes no sense to me because there are no similar columns/rows in either tables.

[v-jiascu-msft]

Hi @Jurre_B,

You have gotten good ideas. But I am afraid there are many things you have to do if I was right. According to your post, your tables look like them in the picture. We will benefit from Power BI if we transform the rows into columns.

1. In Query Editor, choose these columns with “CTRL”, which aren’t the time column.
2. Click “Unpivot other columns”.
3. Rename “Attribute” to “Date”.
4. Click “Clost&Apply.
5. Create a Date table: DateTable = calendar(date(2017,1,1),date(2017,12,31))
6. Create unique Source Table: SourceTable = DISTINCT(UNION(values(Budget[Source]),VALUES(Revenue[Source])))
7. Create relationship.
8. Create report.

Best Regards!

Dale

[Jurre_B]

Hi Dale,

First of all, thank you so much for your detailed assistance!

However, my tables do not look like the one you showed in your pictures.
As stated, my columns are the dates, with the only exception being the first column which consists of the different sources of revenue.
I have imported this table directly from Excel after adjusting a few minor things to make it easier to work with.

The budget (and actual) data looks like this: (note that I removed the data and name of the business for privacy's sake)

Hope this helps!

[v-jiascu-msft]

Hi @Jurre_B

I am a little confused. My table is very similar with yours. Please check again.

Best Regards!

Dale

[tjd]

Don't know if I completely understand what you're trying to do, but I just ran across this video today that shows actual vs budgeted coming from two tables.  Good luck!