Forum Discussion
how to find average
hi i would like to find out the average profit of each genre for the past 10 years .
i have done power BI features like :
using calculate, sum and filter to found out about the sum of gross that year.
i am now looking for ways to find the average of this, how can i do that?
Hi amethyst_tang_2 ,
Try this DAX to create a measure:Measure = AVERAGEX( FILTER( ALL('Table'), 'Table'[Released_Year] >= 2010 && 'Table'[Released_Year] <= 2020 ), 'Table'[Gross] )And the final output is below:
Or you can build a slicer to filter the data from 2010 to 2020:
And now you can just use this DAX to calculate:
Measure 2 = AVERAGE('Table'[Gross])The output will change dynamically according to the slicer you select:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
https://docs.google.com/spreadsheets/d/17_iuqyIl3fk_xfUkgb9fQ-9tJKvwkYvG/edit?usp=sharing&ouid=117276072126307632244&rtpof=true&sd=true
hi the dataset link is here. in summary, i would like to filter out the "released year" to the latest 10 years (2010 to 2020) and find the average of of the gross from 2010 to 2020.hi sample data
Hi amethyst_tang_2 ,
Try this DAX to create a measure:Measure = AVERAGEX( FILTER( ALL('Table'), 'Table'[Released_Year] >= 2010 && 'Table'[Released_Year] <= 2020 ), 'Table'[Gross] )And the final output is below:
Or you can build a slicer to filter the data from 2010 to 2020:
And now you can just use this DAX to calculate:
Measure 2 = AVERAGE('Table'[Gross])The output will change dynamically according to the slicer you select:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amethyst_tang_2 Well in theory you just replace the SUM or SUMX function in your formulas with AVERAGE or AVERAGEX. Otherwise, not enough detail to really provide a solution. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Hi amethyst_tang_2 ,
Here is my sample data:I use this DAX to create a new measure:
Average Profit by Genre = CALCULATE( AVERAGE('Table'[profit]), FILTER( ALL('Table'[Year]), 'Table'[Year] > YEAR(NOW()) - 10 ) )The final output is like below:
If your data table contains only the first 10 years of data, then the DAX only needs to be simplified to:
Average = AVERAGE('Table'[profit])
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
