Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
shanerolle
Frequent Visitor

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

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.

Capture.PNG

 

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

 

 

 

2 ACCEPTED SOLUTIONS
parry2k
Super User
Super User

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

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

amitchandak
Super User
Super User

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/

 

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

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
Super User
Super User

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

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.