Forum Discussion
Average per year
Hi Folks, Need quick help to get average. Sharing sample dataset and expected result.
| Date | Sales |
| 01-Jan-22 | 100 |
| 01-Feb-22 | 125 |
| 01-Mar-22 | 150 |
| 01-Apr-22 | 175 |
| 01-May-22 | 200 |
| 01-Jun-22 | 225 |
| 01-Jul-22 | 250 |
| 01-Aug-22 | 275 |
| 01-Sep-22 | 300 |
| 01-Oct-22 | 325 |
| 01-Nov-22 | 350 |
| 01-Dec-22 | 375 |
| 01-Jan-22 | 200 |
| 01-Feb-22 | 225 |
| 01-Mar-22 | 250 |
| 01-Apr-22 | 275 |
| 01-May-22 | 300 |
| 01-Jun-22 | 325 |
| 01-Jul-22 | 350 |
| 01-Aug-22 | 375 |
| 01-Sep-22 | 400 |
| 01-Oct-22 | 425 |
| 01-Nov-22 | 450 |
| 01-Dec-22 | 475 |
| 01-Jan-23 | 50 |
| 01-Feb-23 | 75 |
| 01-Mar-23 | 100 |
| 01-Apr-23 | 125 |
| 01-May-23 | 150 |
| 01-Jun-23 | 175 |
| 01-Jul-23 | 200 |
| 01-Aug-23 | 225 |
| 01-Sep-23 | 250 |
| 01-Oct-23 | 275 |
| 01-Nov-23 | 300 |
| 01-Dec-23 | 325 |
| 01-Jan-23 | 0 |
| 01-Feb-23 | 50 |
| 01-Mar-23 | 100 |
| 01-Apr-23 | 150 |
| 01-May-23 | 200 |
| 01-Jun-23 | 250 |
| 01-Jul-23 | 300 |
| 01-Aug-23 | 350 |
| 01-Sep-23 | 400 |
| 01-Oct-23 | 450 |
| 01-Nov-23 | 500 |
| 01-Dec-23 | 550 |
Hi MarshalSK ,
We can create a measure.
avg = var _sales=CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[Date].[Year]=MAX('Table'[Date].[Year]) )) var _months=CALCULATE(DISTINCTCOUNT('Table'[Date].[Month]),FILTER(ALLSELECTED('Table'),'Table'[Date].[Year]=MAX('Table'[Date].[Year]))) RETURN DIVIDE(_sales,_months,0)Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi MarshalSK ,
We can create a measure.
avg = var _sales=CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[Date].[Year]=MAX('Table'[Date].[Year]) )) var _months=CALCULATE(DISTINCTCOUNT('Table'[Date].[Month]),FILTER(ALLSELECTED('Table'),'Table'[Date].[Year]=MAX('Table'[Date].[Year]))) RETURN DIVIDE(_sales,_months,0)Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Perfect
calculate ( sum(tblname[sales_col_name]), removefilters( tbl_name[month_col_name], tbl_name[month_nb_col_name]) ) / 12If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution !✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠@Thanks for the reply. Expecting dynamic solution, meaning 12 needs to be dynamic.
considering below scenario
1. month slicer2. if any month doesn't have data
