Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate average of data at different levels by year

Hi 

I have the following Data :

 

CompanyYearGoodwillRevenue
Company A20187735191
CompanyB20187858675
Company A20175435352
CompanyB20172878178
Company A20168936210
CompanyB20165147959
Company A20153067690
CompanyB20155667360

 

I want to achieve a solution where, when I filter year it returns me the average of Goodwill of that year and the previous year. 

So for my year filter 2017: Average of Goodwill in 2017,2016 

                year filter 2016: Average of Goodwill in 2016,2015

                .... and so on

 

The year is in General format (NOT Date format) .. 

 

 

Expected OUTPUT Values: 

 

Company A2018658
CompanyB2018536
Company A2017718
CompanyB2017400.5
Company A2016599.5
CompanyB2016540
Company A2015306
CompanyB2015566
1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

Try this measure

 

Measure = VAR myyear=SELECTEDVALUE(Table1[Year])
RETURN
CALCULATE(average(Table1[Goodwill]),Table1[Year] in {myyear,myyear-1})

Regards
Zubair

Please try my custom visuals

View solution in original post

1 REPLY 1
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

Try this measure

 

Measure = VAR myyear=SELECTEDVALUE(Table1[Year])
RETURN
CALCULATE(average(Table1[Goodwill]),Table1[Year] in {myyear,myyear-1})

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.