Calculating Average question

Hi,

Assuming the numbers in the matrix are the result of this measure

Value = sum(Data[Amount])

try this measure

Value revised = if(hasonevalue(Calendar[Year]),[Value],averagex(values(calendar[Year]),[Value]))

Hope this helps.

You can straight use AVERAGEX to iterate through the value of each year before getting the average

Units Sold Yearly Avg =

AVERAGEX (                         -- Calculate the average across the years

    VALUES ( 'Date'[Year] ),       -- Get each selected year

    [Units Sold]                   -- Calculate Units Sold for each year

)

Note:  AVERAGEX  exclude blank rows. In the example below, although there are 16 years from 1999 to 2014, 1999 is blank so the average is 118500/15 = 7900

zlottt​ 

could you pls provide some sample data (not the table visual) and the expected output based on the sample data?

Hi zlottt​ 

Please try sample pbix.

Please give kudos or mark it as solution once confirmed,

Regards,

Praful

Yes,Use a DAX measure rather than Power Query.

Average 2023-2026 =

AVERAGEX(

VALUES('Table'[Year]),

CALCULATE(SUM('Table'[Value]))

)

This will calculate the average dynamically based on the selected years/filters.

Recommendation Use Power Query for data transformation and DAX measures for calculations like averages, totals, percentages, etc.

Hi everyone,

Sorry for the late reply.

I dont have any measures on the table, the values on the matrix are just a count of number of work orders and I wanted to have an average of the amount of work orders.

Thank you very much everyone.