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
wowosim
Regular Visitor

Comparing actual and budgeted sales transactions (many to many)

Hi, 

 

I have got 2 large tables with sales transactions comparing actual sales and forecasted sales with a few thousand tuples. 

 

Actual Sales

Week

Month

Year 

Company Code (ie AUS-11, AUS-30, MY-12 etc.)

Business Group 

Actual Sales Amount

 

Forecasted Sales

Month 

Year

Country (Australia, Malaysia, China etc.)

Business Group

Forecasted Amount

 

So, my problem is that there exist a many to many relationship between these two tables, with differing transactions between actual and forecasted sales. In some months, the company will forecast sales in certain business groups that do not appear in the actual sales and in certain months actual sales occur in certain business groups or even countries that do not appear in the forecasted table.  Moreover, actual sales is also recorded on a weekly basis. 

 

I thought of creating a relational table which will form a many to 1:1 to many relationship between these two large tables by concatenating week-month-year-business group-company code in the actual sales month-year-country-business group in the forecasted amount such that unique values can be matched to unique values. However, given the above problems stemming from the differing transactions, I am unable to match transactions to each other. Also, a country can consist of many company codes in the actual table. 

 

Ultimately, I would want to create a model that is able to compare actual and forecasted sales between business groups, year, and country. I would really need help on creating this 1:1 relationship between these two tables. Thank you!

 

4 REPLIES 4
wowosim
Regular Visitor

I need help with matching and comparing between two tables, one actual sales and the other forecast sales. Ultimately, I will need to compare these two sales figures based on year, month, country/company code, business groups and whether it is an equipment or service without modifying the data source as much as possible. 

 

Actual Sales

Year

Month

Week

Equipment/Service

Company/Country Code (AU-11, AU-13, MY-34 etc.)

Business group (Domestic Appliances, Heat tech, Physical health management)

Sales Amount

 

Forecasted Sales

Year

Month

Equipment/Service

Company/Country Code (Australia SO, Australasia PMS, SG10 - PMS etc.)

Business Group (DA, IGT, HT, Enablers etc.)

Forecasted amount

 

So far, I have created a look-up table for the date (1/1/2017 etc.) and equipment service as the unique key for these two tables. However, I have difficulty creating the unique key/look up tables to compare the two fact tables on a business group and country level because:

1. The names of the company codes and business groups are different in the fact tables but corresponds to each other

2. But there are some business groups and country/company codes that appear in the actual sales table but not in the forecasted sales table, vice versa

3. There is an unequal amount of country codes and business groups between both tables. In the actual sales table, the there might be 4 company codes representing Australia (ie AUS-11, AUS-12, AUS-33, AUS-49) but only two company codes representing Australia in the forecasted sales table (Australia-S0, Australasia-PMS) but actual sales being equals to forecasted sales for the country in that particular month. 

 

Give such circumstances, how should I model the data? Is it even possible? The only way I can think of is that I replace all the company names with just the country since I only need to slice the data by country and not company code. But the problem of the business groups still remains. I would really appreciate any help from this community as I am rather new to power bi. Thank you! Let me know if my question is too convoluted and more clarification is needed!

ImkeF
Super User
Super User

You should not attempt to connect theses 2 tables directly.

Fact-tables like this should only be connected to Dimension-tables.

 

So what you need are

1) Date-Dimension-table with. Date-Year-Month-Week and a

2) Company table with: CompanyCode-BusinessCode

3) Business-Code table: BusinessCode-Country

Where the first elements are the unique keys.

 

Create a date-field in both your fact-tables by choosing either the first or the last date of the time intervalls as fictional date in order to be able to connect with the Date-Dimension.

 

Imke Feldmann

www.TheBIccountant.com -- How to integrate M-code into your solution  -- Check out more PBI- learning resources here

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Thank you for your reply but I have a few follow up issues:

 

1. Each company in the company code column takes care of a few business groups

2. Business groups in the actual sales table appear in the forecasted sales table using another name ie Domestic Appliances in the actual sales table appear as DA in the forecasted table 

3. There are certain business groups in the actual table that do not appear in the forecasted sales table and vice versa

4. I forgot to mention in my previous post but there is an additional column in both tables that denotes each transaction as either "equipment" or "service" 

 

I might be misunderstanding your solution so I appreciate your kind understanding. 

You might need more Dimension-tables then.

 

Please check out the following articles who explain more about data modelling / the optimal shape for your data in Power BI:

- this article shows that you don't connect your data/fact-tables directly: https://exceleratorbi.com.au/multiple-data-tables-power-pivot/

- and some more background info: https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.