How to integrate tables together - Income Statement

Hi All

Please can you assist with my query

I have 2 tables for the income statement.

  1. Table 1 is an Income statement summary. Data is on a monthly basis. Its named IS data

  2. Table 2 is the Geography data and breaks down the income statement on a segmental basis.

Following on from the example used by Sam in the Financial Reporting showcase course, I am not able to use this example for my query as I would like to merge the Geography Data with the IS data.
The Financial Reporting course initially creates a new table to mirror the Company Expenses table before going ahead to use a UNION function to merge both tables together.

However the Geography data has more than 1 category. It has 7 unique categories which are:

  1. Revenue
  3. Interest Income
  4. Interest Expense
  5. Depreciation & Amortization
  6. Equity Income (Loss) - Net
  7. EBIT

My plan is to have the IS data show the numbers on both a segment analysis as well as a Full Year/ Quarterly analysis

The IS data is a monthly one while the Geography data is quarterly

I am not able to replicate this solution in the Financial Reporting showcase example to my case. Is this scenario possible?

Please see attached my pbix file

Many thanks
Financial Reporting Income Statement.pbix (150.7 KB)

Hello @atin,

Thank You for posting your query onto the Forum.

As you mentioned that you want to merge your two tables under the one roof as @sam.mckay has showed in his Financial Reporting course. But there are several points which are missing in your both the data in order to merge this two tables together. And they’re as follows -

  1. The geographical data is on a yearly basis and the IS data is on a monthly basis. Unless you distribute your geographical figures across a period of 12 months this might not be possible.

  2. The figures in geographical data are bifurcated based on the segments and that to at a yearly level whereas the figures in the IS data are at a monthly level and there’s no segmentation in it. Under the IS Data, you’ll be required to add the segment column and provide how much amount comes from a particular segment because right now that is completely unknown.

  3. Furthermore, in the geographical data the Category column contains the details which are of Sub-Category level in the IS Data and also the Sub-Category column is missing from it.

Therefore, after considering all this points. I’ve come to a conclusion that there’s no similarity between these two tables at all, and hence, as of now, merging of these two tables is not possible.

Once you consider this points and re-structure your data then you may merge the tables.

**Please Note: **For Point No.3, I’m providing a link below where just few days back I’d worked out and shown how one can bifurcate their categories into the sub-categories and overcome that problem. It also contains the Excel and PBIX file which you can refer, if possible, to solve the last point.

Thanks & Warm Regards,

1 Like

Hi @Harsh

Many thanks for your comments.

To clarify my dataset much better. Please note that:

  1. There is a common column between both data sets . The IS data has a column called Sub-Category and the Geography data has a column called Category. As discussed in my earlier email, there are 7 category items within the Geography data which also exists in the IS data

  2. The IS data does not contain segment data. This is the reason why I want to merge them together. The amounts are accurate however I want to merge both datasets together in order to have the segmental (ie from Geography) and sub category columns( ie the IS data) on the same page

Based on your comments now , I will

  • Rename the tables to ensure commonality between both tables

  • Show the geography data on a monthly basis

  • Check out the link on the cashflow statement to show how one can overcome the problem of bifurcating categories into sub categories

Will try this out and see how it goes.

Best wishes

Hello @atin

  1. Well I agree that 7 categories which you’ve mentioned is common between these two tables but just based on that merging cannot be done. Because in Financial Reporting videos you might have observed that “Expenses” table were at a “Monthly” granularity whereas the “Revenues” figures which were coming from the Sales were at a “Daily” granularity and therefore, @sam.mckay had to ensure that before merging this two tables together his revenues which were at a daily granular level is being converted to a monthly granular level so that when the tables are merged together there’re no two different granular levels of data within the merged table. Now, in your case, one was at a “Yearly” granular level and the other is at “Monthly” granular level.

  2. In point 2, as you’ve agreed that segment column doesn’t exist in the IS Data. Now, since that column is not there when you try to merge this two tables together, in first half of the merged table you’ll get results only against the data which had segments information and in the second half of the merged table you’ll simply receive “Blanks” because the segment information is not available under the IS Data.

  3. Once you bring both of your data at the same granular level just remember that upon merging this tables your figures will inflate for those 7 categories. For example, you’ve Depreciation and Amortization in both of your tables and that to it the figures are also for the same year period. Now when we merge this two tables it will simply add up this figures when you try to make any analysis. If I elaborate this further, here’s the example given below -

Assuming you’ve Depreciation figure $100/- each at a monthly level for the year 2017 in your IS Data which is equal to $1,200/- yearly and now this same figure you’ve in your geographical data at a yearly and at segmented level. Now, when you bring your geographical data at a monthly level and merge them together the effect will be of $2,400/- instead of $1,200/-. Ideally what was required was only to bifurcate out the figures at a monthly granularity level and that to for each segment. And due to this effect it will simply lead to an error, which in accounting terms you can call it as an “Error of Commission”.

The point to be noted over here is that, @sam.mckay had merged the “Expenses” table with the "Revenues" table which’re distinctive in their nature. Now, what you’re trying is to merge the tables which posses the same characteristics. So just to make you aware of this result I took up this example.
If you’re unaware of this result/effect. Kindly consider it before you start making any such modifications/altercations in “Both” of your tables.

Thanks & Warm Regards,

Hi @atin, we’ve noticed that no response has been received from you since the 31st of July. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!