cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## How to Calculate the Avg. and sum from different tables

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
1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Super User

@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])})

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors