How to Calculate the Avg. and sum from different tables

[Juan_CGR94]

Hi comunity,

I have this data to analyze, I have three different tables (below): Package, Raws and Product. each has the same SKU's and its Cost per month. I need first to calculate the average cost per category (Package, Raws and Product) between the SKU's per month (e.g. average cost between A, B and C in January, in February, etc.)

The expected result will be the average package cost for January between A, B and C, the average package cost for February between A, B and C, and so on.

Then I need to sum the calculated average of each category (avg. package cost for Jan + avg. raws cost for Jan + avg. product cost for Jan) and repeat this for each month. and plot this in a Matrix (Months in columns and SKU in rows, and the sum of the avg. package + avg raws + avg. product would be the values)

Thanks a lot,

Package
SKU	Month	Cost
A	January	145
A	February	105
A	March	126
A	April	143
B	January	133
B	February	149
B	March	120
B	April	139
C	January	115
C	February	130
C	March	116
C	April	111

Raws
SKU	Month	Cost
A	January	221
A	February	229
A	March	215
A	April	231
B	January	228
B	February	230
B	March	248
B	April	234
C	January	243
C	February	201
C	March	231
C	April	201

Product
SKU	Month	Cost
A	January	437
A	February	443
A	March	434
A	April	441
B	January	409
B	February	416
B	March	402
B	April	415
C	January	447
C	February	421
C	March	450
C	April	441

[V-lianl-msft · Accepted solution]

Hi @Juan_CGR94 ,

1. Add a column as the table name for each table first in the query editor.

2. Append queries as new.

3.Create measures

avg_table&month = CALCULATE(AVERAGE(Append1[Cost]),ALLEXCEPT(Append1,Append1[Month],Append1[table]))

sum_avg = 
var sum_3table = CALCULATE(SUMX(Append1,[avg_table&month]),ALLEXCEPT(Append1,Append1[Month],Append1[SKU]))
return IF(ISINSCOPE(Append1[SKU]),sum_3table,sum_3table/3)

Sample .pbix

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

[amitchandak]

@Juan_CGR94 , Create a month/date and SKU and any common tables an then do like

One of the measure should work against common table

Sum(Package[Cost])+ Sum(Raw[Cost])+Sum(Product[Cost])/3

average(Package[Cost])+ average(Raw[Cost])+average(Product[Cost])/3

average(list{average(Package[Cost]), average(Raw[Cost]),average(Product[Cost])})