How to calculate sum of different table which separated by years and what is the best data model to

[rane19]

Desire Result

Choose year from Slicer , return sum result of Scorecard . 

 Problems No single measure can be putted in scorecard and to apply for calculating all tables while year is selected by slicer .

e.g.
sum(table20[Score])
sum(table21[Score])
sum(table22[Score])

• Have to avoid append table as each tables of a single year contains over millions of rows.
• All tables are imported from power bi dataflow without using Direct Query.

Data

table20
YEAR    GEO Type    Score
2020    Asia    A   1
2020    Africa  A   2.5
2020    CentralAfrica   A   0
2020    Europe  A   0
2020    MiddleEast  A   0
2020    America A   1.5
table21
YEAR    GEO Type    Score
2021    Asia    A   3
2021    Africa  A   2
2021    CentralAfrica   A   6
2021    Europe  A   1
2021    MiddleEast  A   2
2021    America A   8
table22
YEAR    GEO Type    Score
2022    Asia    A   4
2022    Africa  A   0
2022    CentralAfrica   C   3
2022    Europe  C   4
2022    MiddleEast  A   1
2022    America A   5

Relationship Calendar table link those tables .

Calander = CALENDAR(DATE(2020,1,1),DATE(2022,12,31))

What is the best way to achieve this without appends to be a larger dataset ? 

Not sure "aggregation" based on "imported table" can help on the performance ? Please advise if my concept is basically wrong like append table cannot be avoided  .e.t.c.

[rane19]

Thanks both Aburar_123 & m3tr01d comments . Going back to Azure with ETL approaches will be always my first perferences .If the situation that the constraint that I can only handle in Power Query , I have append & making key to restructure and long list Facttable (> 5 milions rows) . Do you think there is any funciton/setting like turning off reload of the full table everytime a perform the Key column making process to achieve a start schema ?  Currently 1st ~5th Key column making cause > 5mins of loading in Powr Query , the 10th ~> 15th Key column making stucking more than 30mins....

[V-lianl-msft]

Go to Power Query Editor then right click on the table and uncheck 'Include in report Refresh'.

See if this can improve your report performance.

[m3tr01d]

@rane19 
The best practice for the Datamodel is to have one Fact table and one dimension. 
Storing it as table20, table21, table22 will be awfull for performance and it adds a lot of complexity to the measures you will create.

Follow @Aburar_123 advice and do proper ETL to store the data in one table in a SQL database.

[Aburar_123]

Hi,

if you add it as a seperate table you need to create relation manually whenever you add new table.

If you append it everytime into a table that also will be a manual work. 

These manual work would be painful for longer run.

So, my suggestion is to load these data into Azure sql server or On-prem sql server into a single table means append it every time using ADF or SSIS or any other ETL tool.

In this way you can reduce the manual work considerably and due to single table your report performance will be good due to the auto tuning by vertipaq engine. 

If you dont want to use any ETL tools then my suggestion is to append the data into a single table and use it in Import mode.

Please like my post if it helps you. Thanks.