cancel
Showing results for
Did you mean:
Helper I

## Calculate Total for the Current Year

I have the following table:

 ID Date Description Date Month Date Year Sales 1 Dec-2020 12 2020 100 2 Jan-2021 1 2021 100 3 Jan-2021 1 2021 50 4 Feb-2021 2 2021 100 5 Mar-2021 3 2021 25 6 Mar-2021 3 2021 75 7 Apr-2021 4 2021 100 8 Apr-2021 4 2021 100

I would like to get the following table:

 Date Description Monthly Total Overall Year Total Average Year Total Jan-2021 150 550 137.5 Feb-2021 100 550 137.5 Mar-2021 100 550 137.5 Apr-2021 200 550 137.5

I was already able to get the Monthly Total just by summing Sales column. How do I create a measure / calculated column to compute for the Overall Year Total and Average Year Total?

1 ACCEPTED SOLUTION
Community Support

Hi @lancea

You can create measures to return Overall Year Total and Average Year Total .

Monthly total = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Date Description]))

Overall Year Total = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Date Year]))

Average Year Total = [Overall Year Total]/CALCULATE(DISTINCTCOUNT('Table'[Date Description]),ALLEXCEPT('Table','Table'[Date Year]))

Then put the measures with fields ‘Date Description’ and ‘Date Year’ in a new table visual ,like this

In ‘Date Year’ filter , filter out data for 2020 .

I have attached my pbix file , you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

3 REPLIES 3
Community Support

Hi @lancea

You can create measures to return Overall Year Total and Average Year Total .

Monthly total = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Date Description]))

Overall Year Total = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Date Year]))

Average Year Total = [Overall Year Total]/CALCULATE(DISTINCTCOUNT('Table'[Date Description]),ALLEXCEPT('Table','Table'[Date Year]))

Then put the measures with fields ‘Date Description’ and ‘Date Year’ in a new table visual ,like this

In ‘Date Year’ filter , filter out data for 2020 .

I have attached my pbix file , you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

Super User IV

@lancea , Try measures like, with help from date table

This year Today =
var _min = today()
return
CALCULATE(sum('Table'[Sales]), FILTER('Date','Date'[Date] = year(today()) ) )

overall This year Today =
var _min = today()
return
CALCULATE(sum('Table'[Sales]), FILTER(ALL('Date'),'Date'[Date] = year(today()) ) )

overall avg This year Today =
var _min = today()
return
CALCULATE(Avergae('Table'[Sales]), FILTER(ALL('Date'),'Date'[Date] = year(today()) ) )

Proud to be a Super User!

Helper I

Thank you for this! The Overall Year Total works! However, I am getting incorrect result from the average as seen in the screenshot below:

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.