Need help writting a measure to average for three years

[computermike]

I have a Report for 2016 that shows sums of rev by product.  I have graphs and filters for Area, Product line, month and my main graph shows rev by location.  The grain of the data is Rev aggregated by Product line, Month & location ( hierarchy = location, zip, sales area).  So I have one record for Feb at location "W" for Product "STW" and the rev.  All my data is for 2016 and that is the title of my report.

My model include a Product Line, Location and Date dimension and a Rev fact table.

Now I want to reproduce the exact same report, but instead of showing data for 2016.  I want to show data for 2014, 2015 and 2016.  I want to show the averages for these three years.  So my title will be 2014-2016 Averages..  instead of 2016.

I recreated the same data model with data from all three years.  Now I just need to created a measure that average the three years.  Then I can sum it and place it in my report.  Only problem is I am not sure how to write this measure.  Once I write it I will just sum it so the Sales Area totals, Product line Totals will sum correctly.

Can anyone help me get started with this measure, and/ or comment if they see anything wrong with this approach?

Thanks,

Mike

[computermike · Accepted solution]

I figured it out.

sum(fRev[RevAmt])/DISTINCTCOUNT(dDate[Year])

[computermike]

When I first wrote that I used DISINCTCOUNT(Related(dDate[Year]) but got an error.  I am surprised that gave me an error.  I am on chapter 3 of the definative guide to Dax and it mentioned that if you ever used a column from a different table you needed the "Related" keyword.

I could never get the Average function to work.  It was always just averaging every row.

I guess DAX is a learnng curve. 

[Phil_Seamark]

You could start with

Average Rev = CALCULATE(Average('FactTable'[Rev]))

And see what that gives you.