Trying to combine old and new software

[EmilePl]

Hi,

Last year, we started rolling out a new ERP system that's fundamentally different from our old one.
Previously, when combining multiple sources, i would try to align the structure of the SQL query and append/union them.
Now I'm trying to use separate fact tables but with common dimensions, but i'm running into trouble when a dimension exists in the new software, but not in the old one.

I've simplified it in an example, where the "region" dimension was not available in the old software:
My two fact tables look like this

My relationships like this

I created 3 measures:

Turnover old software = SUM('Old software'[Turnover])
Turnover new software = SUM('new software'[Turnover])

Turnover overall = Turnover old software + Turnover new software

Now, when i create a table that attributes the overall turnover to the regions i would expect something like this

Year	Region	Turnover overall
2021	Blank	5.000.000
2022	Blank	6.000.000
2023	Blank	7.500.000
2023	Europe	1.200.000
2023	USA	3.000.000

Since it can't attribute the turnover from the old software to a region, it should create a blank category.
But instead, it's adding the turnover it can't attribute to each category, like this:

Is this expected behaviour? If so, can you help me understand why?
Is there a solution?

Thank you!

[Anonymous · Accepted solution]

Hi @EmilePl ,

Based on my test results, it seems that this behaviour may occur because of automatic aggregation of processed data

When you join the region it will give him an equal share of the turnover depending on the date.

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.