Forum Discussion

pani_victoria's avatar
pani_victoria
Helper III
1 year ago
Solved

average for each year

Hello colleagues!

Please help!


I need to calculate average sales for each year without taking into account the last (incomplete) month.

My dataset looks like this:

 

I want to get the average for full months of the year.

 or 

 

And if I expand the table to stores, then I should get the average for the year for each store.

My gratitude for your help!

Need Help
Show and Tell
Tutorial Requests
  • sevenhills's avatar
    sevenhills
    1 year ago

    For yearly average

     

    Avg Sales Per Year = 
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)

     

     

    If not please could you paste the data from excel to here, so that anyone of us can try

2 Replies

  • sevenhills's avatar
    sevenhills
    Super User
    1 year ago

    For yearly average

     

    Avg Sales Per Year = 
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)

     

     

    If not please could you paste the data from excel to here, so that anyone of us can try

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    Share data of a few months and year.  Share the download link of the MS Excel file.