Finding year to date total of average values

[Randomsomeone]

Hi everyone, I am new to Power BI and need help with finding the average of a set of average values. (I know it sounds funny but hear me out) 

So has you can see on the screenshot I have a matrix that shows the average deal size across months. While that works out fine I am unable to generate a column that shows the average of all these average values. Does anyone know how to solve this? How can I simply have a card on my chart (see screesnhot) that basically is an average of all the average values? I have been stuck on this since past 24 hours so any help would be great! 

[Anonymous · Accepted solution]

Hi @Randomsomeone ,

You'll need to use aggregation and iteration functions to achieve this effect. Refer to below formula to create measure:

AvgDealSize = AVERAGE('YourTable'[DealSize])

AvgOfAvgDealSize =
IF (
    ISFILTERED ( YourTable[Month] ),
    [AvgDealSize],
    AVERAGEX ( VALUES ( 'YourTable'[Month] ), [AvgDealSize] )
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Randomsomeone , You can avg and overall measures like

YTD montly Avg = CALCULATE(Averagex(Values('Date'[Month Year]), CALCULATE(SUM(Sales[Sales Amount])),DATESYTD('Table'[Date])))

Overall YTD montly Avg =CALCULATE( [YTD montly Avg], allselected())

if needed you can replace month year with date or week or qtr based on the level on which you need Avg

Avg of Sum : https://youtu.be/cN8AO3_vmlY?t=22980

[Randomsomeone]

Thanks for your response, I got an error in DAX for YTD montly Avg saying that "Too many arguments were passed for AverageX function"