How can I compare value from 2 datasets that are unconnected?

[shanerolle]

Hello everyone,

I am attempting to compare numeric values from 2 datasets (I'm not sure if dataset is the correct term, so correct me if I am wrong) that are unconnected and hopefully applying conditional formatting to make it easy to visually see how they compare. 

The situation is I have two datasets of information that I cannot link together, they contain the columns:

1) Company, Account Number, Date, Expense Amount, Description, and other general information

2) Company, Account Number, Date, Budget Amount (for each month), and other general information

I have tried to link the datasets together by account number since that is what I want to be analyzing the information by, but it only allows a many to many connection and does not work correctly. There are around 20 companies, hundreds of account numbers in both datsets, millions of indiviudal expenses in dataset 1, and thousands of budget values for the varying account numbers and months in dataset 2.

Essentially I want to be able to take set 1 and have it sum up the total amount of the expenses for each account number for whatever filtered date range I set it to. There are a large number of expenses, all of varying amounts, so I have to sum them in order to get the total by month. I then want to be able to take the total expenses for that month, and compare it to the budget for that account number for that month, and based on the values, conditionally format the display so it is easy to see if you are under budget, nearing your budget, or over budget. 

The problem I am running into is that I have the information displayed side by side in two different matrix tiles since I cannot link the two data sets, but I have no idea how I can compare one value to another if they are not linked, or if it is even possible. Again, I am very new to using Power BI, as I only started in it about a week and a half ago, so maybe I am coming at this from the completely wrong angle, and all suggestions are appreciated. 

These are the two datasets.

If there is any other information or visuals needed to better understand what I am attempting to do, please let me know, I can create a sample, I am just limited in the information I can share.

Thank you,

Shane

[parry2k · Accepted solution]

@shanerolle it can be easily done wiht proper data model. you need to add two following tables in your model:

- account table -> it has unique account numbers

- calendar table -> it is best practice to have a calendar/date table in your model, there are many article on it on how to add one

one above two tables are there, set relationship of your actual and budget table with these two tables, basically account table will have one to many relationship wiht actual and budget and similarily calendar table will also have the relationship

in any visual, you use account and date from these new tables and sum values from your actual and budget, you will see correct information from both the tables for each account and period.

Would appreciate Kudos 🙂 if my solution helped.

[amitchandak · Accepted solution]

You need to create a few dimension

Company = distinct(union(all(table1[Company]),all(table2[Company])))

Account Number =distinct(union(all(table1[Account Number]),all(table1[Account Number])))

Date :

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Join them back with both tables

Also refer

https://docs.microsoft.com/en-us/power-bi/guidance/

[parry2k · Accepted solution]

@shanerolle it can be easily done wiht proper data model. you need to add two following tables in your model:

- account table -> it has unique account numbers

- calendar table -> it is best practice to have a calendar/date table in your model, there are many article on it on how to add one

one above two tables are there, set relationship of your actual and budget table with these two tables, basically account table will have one to many relationship wiht actual and budget and similarily calendar table will also have the relationship

in any visual, you use account and date from these new tables and sum values from your actual and budget, you will see correct information from both the tables for each account and period.

Would appreciate Kudos 🙂 if my solution helped.