'Is there a function to sum averages with multiple selections in a slicer in power BI/ dax?
I'd like to make a visual with average amount (totalled per one or more teams) per month (while keeping date hierachy to show data per day).
I have a table with amount per team per day.
| Date | team | Amount |
|---|---|---|
| 1-1-2022 | 1 | 25 |
| 1-1-2022 | 2 | 25 |
| 1-1-2022 | 3 | 25 |
| 1-1-2022 | 4 | 25 |
| 2-1-2022 | 1 | 25 |
| 2-1-2022 | 2 | 25 |
| 2-1-2022 | 3 | 23 |
| 2-1-2022 | 4 | 15 |
| 3-1-2022 | 1 | 27 |
| 3-1-2022 | 2 | 29 |
| 3-1-2022 | 3 | 23 |
| 3-1-2022 | 4 | 15 |
| 4-1-2022 | 1 | 27 |
| 4-1-2022 | 2 | 29 |
| 4-1-2022 | 3 | 19 |
| 4-1-2022 | 4 | 15 |
| .. | .. | .. |
| 31-1-2022 | 1 | 25 |
| 31-1-2022 | 2 | 10 |
| 31-1-2022 | 3 | 15 |
| 31-1-2022 | 4 | 29 |
(See excel example below)
At the moment i'm using the [amount] and [date] fields to feed he visual, no measures.
The user can select one or many teams with a slicer to show different averages per month. Selecting one team gives the desired result.
But...
Selecting two or more teams gives a logical, but undesired result. It shows the average of team averages per month. Desired would be to see the sum of averages per team per month.
ie if for the first month team 1-4 have averages of 25 23.5 22.7 and 27.5 the result of all teams is now 24.68 (avg of avg). Desired would be 98,7 (sum of avg)
Is there a way to do this with a dax measure?
Solution 1:[1]
Why are you using an iterator? Seems like the granularity is the same as the one in the table. You can simply use AVERAGE and remove context transition with ALL.
DAX Calculation
Avg. =
CALCULATE ( AVERAGE ( 'Table'[Amount] ), ALL ( 'Table'[Date] ) )
Output
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Angelo Canepa |