Calculate and plot an Average from sales per year

[hierosir]

Hi guys!

I have a measure that is a TransactionAmount.  I then have Financial Year Codes (FY14, FY15, FY16, FY17).  I have a bar chart which will show the total sum of TransactionAmount for each Financial Year.  Essentially showing total sales for each year.

I want to create a line to go across the graph which will show the average yearly sales.  As the Chart does show the current financial years sales (still underway), I want to have this average calculated from all PRIOR financial years, not including the current (FY17).

How can I make this value?

Values in question here:

Table: viw_rprt_transactions

Measure: TransactionAmount

Column: FinYear_Code 

Chance for some help :D?

Cheers,
Hierosir

[technolog · Accepted solution]

Create a measure to calculate the total sales for each financial year:
If you haven't already created a measure for the total sales for each financial year, you can do so with this formula:

TotalSalesPerYear = SUM(viw_rprt_transactions[TransactionAmount])
Calculate the average yearly sales for all years except the current one:
This requires filtering out the current financial year, and then averaging the total sales for the remaining years. Here's how:

AvgYearlySales =
VAR CurrentYear = MAX(viw_rprt_transactions[FinYear_Code])
RETURN
DIVIDE(
CALCULATE(
[TotalSalesPerYear],
viw_rprt_transactions[FinYear_Code] <> CurrentYear
),
COUNTROWS(
FILTER(
ALL(viw_rprt_transactions[FinYear_Code]),
viw_rprt_transactions[FinYear_Code] <> CurrentYear
)
)
)
Here's a breakdown of the formula:

We first get the current financial year using the MAX function.
We then use the CALCULATE function to compute the total sales for all years except the current year.
The COUNTROWS function counts the number of distinct financial years (excluding the current year).
Finally, we divide the total sales (for all years except the current year) by the number of those years to get the average yearly sales.
Add both the TotalSalesPerYear and AvgYearlySales measures to your chart:
Use the TotalSalesPerYear measure for your bars to represent each financial year's sales.
Use the AvgYearlySales measure as a constant line across your bar chart to represent the average sales of prior financial years.
Now, your chart should show the total sales for each financial year as bars and the average sales of prior years as a line. Adjust the formula as needed based on your actual table and column names if they differ from the ones provided.