cancel
Showing results for 
Search instead for 
Did you mean: 
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.

 






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/

 



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!

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/

 



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!
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.

 






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
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors