cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mazharmh
Helper III
Helper III

How create relationship between budget sheet and Sales table

Hi All,

 

I have problem creating relationship between budget and sales table

 

1. I have budget in Excel workbook, Consists of Monthly and weekly budget

2. Need to compare monthly and weekly budget  with Total Sales, Gross Profit and Gross Margin.

3.Compare State wise, Sales category wise , Month and Week wise.

 

 

What I did , created another workbook for distinct Month number, Month name and year and created a realationship between budget and sales table, it only works with month number but I cant compare it state wise and category wise. Can any one please help me out.relation shiprelation shipbudgetbudget

10 REPLIES 10
Phil_Seamark
Microsoft
Microsoft

Hi @mazharmh

 

I would break the relationship between budget and sheet1, and instead create a relationship between budget and your date table.

 

I would also switch the bi-directional filters to be single directional.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark

 

Thanks for your reply,

 

When I am trying to create a relationship between Budget(Month Number) and Date Table(Month Number). I am getting following error. So I have created extra sheet with only unique values to solve this problem. image.png

Hi @mazharmh

 

Maybe connect your budget table to the Date column (not the month column) in your date table


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

HI @Phil_Seamark,

 

 

I dont have Date coloumn in my budget table, It's monthly budget.

 

 

Thanks

Mazhar

Is your [Calendar Month] column a date field?  I understand your budgets are set monthly, but if you have a date value that is the 1st of every month, you will still be able to create useful reports

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark,

 

 

Calender Month coloumn is set as Whole Number .

HI @mazharmh

 

Can you add a date column to that table?

 

How big is your data file?  If you share it to me privately I can tidy it up if you like


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark,

 

You want to me add Date coloumn to Budget Table???. Date table already have Date coloumn. How can I Share file with you?

 

Thanks.

HI @mazharmh

 

Upload your PBIX file to a OneDrive, or Dropbox and send me a direct message with a link to it.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark,

 

 

I have added one more column to my budget table(Month start date ) and tried connecting to Date Table - Sales Table.

But still, I am not able to filter budget by Warehouse and category. 

 

Thanks

Mazhar

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.