How to Calculate the average of rows (including blanks) from different tables

[Anonymous]

For example I have 3 different tables with same identifier.
How can I calculate the average of the rows(months)?

ID	Jan	Feb	Mar
1	2	5
2	7		3
3	5	6	6
4		1	2

ID	Apr	May	Jun
1	3	9	3
2		4	5
3	10	6	7
4	3	7	4

ID	Jul	Ago	Sep
1	5	8	6
2	6	10
3	10	4	9
4		9	5

I want to have the following output (the rows combined based on Unique ID to calculate Average (counting blanks)):

ID	Jan	Feb	Mar	Apr	May	Jun	Jul	Ago	Sep	Average
1	6	9		4	9	3	7	3	5	5.111111
2	10		5		9	6	3	6		4.333333
3	2	9	10	1	9	9	7	3	4	6
4		1	9	8	1	8		8	8	4.777778

thanks,

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

According to your description, i think it can be realized in Power Query Editor.

Here are the steps you can refer to :

(1)This is my test data which is the same as yours: 'Table1' and 'Table2' and 'Table3'

(2)We can put this M language in "Advanced Editor":

let
    Source = Table.NestedJoin(Table1, {"ID"}, Table2, {"ID"}, "Sheet1", JoinKind.FullOuter),
    #"Merged Queries" = Table.NestedJoin(Source, {"ID"}, Table3, {"ID"}, "Sheet2", JoinKind.FullOuter),
    #"Expanded Sheet1" = Table.ExpandTableColumn(#"Merged Queries", "Sheet1", {"Apr", "May", "Jun"}, {"Apr", "May", "Jun"}),
    #"Expanded Sheet2" = Table.ExpandTableColumn(#"Expanded Sheet1", "Sheet2", {"Jul", "Ago", "Sep"}, {"Jul", "Ago", "Sep"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Sheet2", "Average", (x)=> List.Sum(List.Skip(Record.ToList(x)))/List.Count(List.Skip(Record.ToList(x)))     )
in
    #"Added Custom"

(3)Then we can meet your need , the result is as follows:

If you want to use dax to calculate the Average , you can unpivot the table like this:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

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

[onurbmiguel_]

Hello, 

Can you sahre what will be the ouput that you want . 

In your model you have one fact table and one calendar table ? 

BR 

Bruno 

[Anonymous]

I updated it 🙂
I have 3 tables in my model (actually I have more but these 3 related to this issue)

[onurbmiguel_]

Hi again 

Can you share a pbix with dummy values?

You need to change your model, you can't have 3 different tables and then ask to make a calculation on the 3 at same time.

BR 

Bruno 

[Anonymous]

I appended the tables but then I got a different question:
How Combine rows based on Unique ID? Please click on the link
https://community.powerbi.com/t5/Desktop/Combine-rows-based-on-Unique-ID/m-p/2805715

[onurbmiguel_]

Hi 

You nedd to have something like this 

the tips is to use the first day (or the last day) of the month in the date column from fact table so you have a relationship 1 -> *

after you can use the Year and month from the calendar table into the visual 

Best regards

Bruno Costa | Responsive Resident

Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 👍

Take a look at the blog: PBI Portugal