Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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:
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |