How to calculate a sum of averages of specific items

[efanta]

Hi Folks,

I´m trying to calculate the sum of different averages of elements that have an assigned criterion. I will explain it in an example that I prepared:

For different branches (A to J) od a company, I must add the averages of each one only if they have had at least 3 sales during the year (in the table appear at least 3 times). For this case, I have to add the average sales of A and the average sales of B.

Month	Name	Sales
Jan-17	A	12
Jan-17	B	14
Jan-17	C	2
feb-17	E	3
feb-17	F	5
feb-17	A	16
feb-17	B	22
mar-17	H	54
mar-17	A	11
mar-17	B	34
mar-17	J	44

I have tried many formulas but I can not reach the value! 

The result should be:

Criteria - Count	>=3
A - Average	13
B - Average	23,33
sum A av + B av	36,33

Please, if someone manages to help me, I will be grateful!

[MarcelBeug · Accepted solution]

Solution in Power Query (M).

You can use group by on the Transform tab and fill out the popup:

Note: if you have data of multiple years, then you should add a group for the year as well.

Then filter on Count >= 3 and remove the Count; example code:.

let
    #"Grouped Rows" = Table.Group(SalesData, {"Name"}, {{"Count", each Table.RowCount(_), type number}, {"Average", each List.Average([Sales]), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Count] >= 3),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count"})
in
    #"Removed Columns"

Result in a Matrix visual: