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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Employee
Employee

Hi @Anonymous

 

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!

Anonymous
Not applicable

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

 

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!

Anonymous
Not applicable

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!

Anonymous
Not applicable

Hi @Phil_Seamark,

 

 

Calender Month coloumn is set as Whole Number .

HI @Anonymous

 

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!

Anonymous
Not applicable

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

 

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!

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.