Forum Discussion
Total Average measure
I am wondering if anyone knows of a Avg measure that would easily give the output in table 2 below. The measure (attached below) gives the output shown in table 1 and this isn't what I want. The aim is to have a chart similar to that shown at the bottom of this post below with the average line across the middle of the chart.
Average =
CALCULATE(
AVERAGEX(
KEEPFILTERS(VALUES(dim_date[MonthYear])),
Query1[rowcount]),
ALLSELECTED(dim_date[MonthYear])
)
| Table 1 | Table 2 | ||||
| MonthYear | Number of Contacts | Average | MonthYear | Number of Contacts | Average |
| Apr-17 | 5177 | 5177.00 | Apr-17 | 5177 | 5544.83 |
| May-17 | 5771 | 5771.00 | May-17 | 5771 | 5544.83 |
| Jun-17 | 5804 | 5804.00 | Jun-17 | 5804 | 5544.83 |
| Jul-17 | 5839 | 5839.00 | Jul-17 | 5839 | 5544.83 |
| Aug-17 | 5284 | 5284.00 | Aug-17 | 5284 | 5544.83 |
| Sep-17 | 5394 | 5394.00 | Sep-17 | 5394 | 5544.83 |
| Total | 33269 | 5544.83 | Total | 33269 | 5544.83 |
Anonymous You don't generally see a SUMMARIZE used with 2 different tables.
6 Replies
Anonymous Use the Analytics Pane?
Or This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.Thanks for your response Greg. I am currently using 2 tables to populate my chart; a monthyear column in my calendar table and the sum total of contacts called Rowcount for each monthyear from a table called Query1. If i am using your 'AvgScoreMeasure', what value do i enter for the following;
- Table
- Table[Group]
- "Measure"
- [YourMeasure]
I tried the dax below but it didn't give the average output in table 2. Is there something I have possibly done wrong?
Mean = AVERAGEX( SUMMARIZE(dim_date,dim_date[MonthYear],"MeasureNew",Query1[rowcount]),[MeasureNew])