cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
suzsword Frequent Visitor
Frequent Visitor

Calculating a yearly average and displaying in a table that is only showing the current day

I need to be able to show an average per year per group and show in a table that has been filtered to only show the current day. 

 

My measure is 

YTD (BOPD) = calculate(average(Actuals[bopd]),
    filter( all(Actuals), Year(Actuals[full_date])) = year(today())))

The resulting table (which has a visual level filter of showing only today) shows a single value for the YTD figure across all of the groups. I need an average calculated by each group. 

 

filteredtable.PNG

 

Its as if the calculation is ignoring the relationship and I dont know how to get it to use it. 

 

Relationship.PNG

 

Any help would be appreciated.

 

Susan

1 REPLY 1
Community Support Team
Community Support Team

Re: Calculating a yearly average and displaying in a table that is only showing the current day

Hi @suzsword 

I am not certain how you want to "average" the values yearly,

if you want "average" yearly="sum values of 2018"/"total days of 2018, just replace "SUM" function with "AVERAGE" in my [Measure] below.

Take "SUM" function as a test to illustrate clearly.

Create measures

Measure = CALCULATE(SUM(Sheet7[bopd]),FILTER(ALL(Sheet7),Sheet7[year]=MAX(Sheet7[year])&&Sheet7[group name]=MAX(Sheet7[group name])))

Measure 2 = IF(YEAR(TODAY())=MAX(Sheet7[year]),1,0)

add measure2 in the visual level filter

9.png

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.