cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Referring to another Table by calculating average

Hi community,

I have the following case:

Sales

 Date Sales New Column (average Temperature) 2024-01 500 2024-02 300 2024-03 400

Weather_data:

 Date temperature 2024-01 3 2024-01 5 2024-01 2 2024-02 3 2024-02 11 2024-03 2 2024-03 3

The tables are connected (Column: date)

I want to create a new calculated Columnin table Sales, which calculates the average temperature each month. In this case, table Weather_data has multiple rows for each month and table Sales only has 1 row each month.

Example for first row (2024-01) in "Sales": 3+5+2 => average =>3.33

Example for second row (2024-02) in Sales: 3+11 => average => 7

Example for third row (2024-03) in Sales: 2+3 => average => 2.5

Expected result for the new column in table "Sales":

 Date Sales New Column (average Temperature) 2024-01 500 3.33 2024-02 300 7 2024-03 400 2.5

What would be the DAX function for the new calculated column?

Thank you!

Best

1 ACCEPTED SOLUTION
Resolver II

Based on your sample data it would be this

``````Avg Temp =
CALCULATE(
AVERAGE(Weather_data[temperature]),
FILTER(
Weather_data,
Weather_data[Date] = Sales[Date]
))``````

however this was based on the date column you provided, if you are using an actual date instead of the year-month text in Weather_data I would highly recommend adding a calendar table.

Resolver II

Based on your sample data it would be this

``````Avg Temp =
CALCULATE(
AVERAGE(Weather_data[temperature]),
FILTER(
Weather_data,
Weather_data[Date] = Sales[Date]
))``````

however this was based on the date column you provided, if you are using an actual date instead of the year-month text in Weather_data I would highly recommend adding a calendar table.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors