Getting average of a measure and plotting as a trend line

[Abhaykumar]

I have a measure as following: 

Normalized Events = (IF ((COUNTROWS(FILTER('Events',[Type]="TypeA")) = 0), 0,COUNTROWS(FILTER('Events',[Type]="TypeA"))))/ COUNTROWS(FILTER('Population',[Type]="TypeA"))

This is plotted on a line chart with dates as x-axis. I also want to show a mean value as another line on the chart. How can i get the average of this measure as a single value that can be plotted as a horizontal line?

[CheenuSing]

@Abhaykumar

1. I created a scenario where in I have the sales data by date and there are more than 1 sales record for a day. This is called BaseSales

Date	Sales
20160301	20
20160301	30
20160301	50
20160304	25
20160304	45
20160304	40
20160309	35
20160309	55
20160404	15
20160404	34
20160404	90
20160404	45
20160404	75
20160404	40
20160420	120
20160420	130

2. The first step  is to get the averages by day and store it as a table created dynamically. 

3. To do this selecte new table from modelling and enter the code as follows

    AverageByDay(TableName) = Summarize( BaseSales, BaseSales[Date], "ByDayAverage",Average(BaseSales[Sales]))

4. What this actually does is creates an average by day from the base sales data and stores in the column ByDayAverage and stores the entire rows as table AverageByDay.

5. Create a mesaure AvgofAvg =Calculate(Average([ByDayAverage]),AllSelected(AverageByDay[Date]))

6. From this new table AverageByDay create the combo chart with Date as Shared Axis, ByDayAverage as ColumnValues and AvgofAvg as LineValues.

7. The chart will be the way you wanted.

Try it out and if you have any problems do reply to the post. Send me your mail id so that I can send you the pbix file.

If you find the solution working , please accept it as a solution and also give Kudos.

Cheers

[v-caliao-msft]

@Abhaykumar， You can add a calculated column using the same DAX expression , and then add this new column to Values.

Regards，

Charlie Liao

[Abhaykumar]

@v-caliao-msft, I did the same thing but the average does not seem to be correct. As from the following screenshot, the average is 0.18 but when the column is plotted it shows average to be 0.20. I also added a second column with following DAX : 

AvgCol = AVERAGE(Event41[TestCol]). But this gives the same result of 0.20. Interestingly median also gives the same results.

[Anonymous]

A bit of a guess here, but this sounds reasonable to me:

Median Event := MEDIANX(Calendar, [Normalized Events])

just drop this measure on your on your chart... maybe? 🙂