Average YoY changes by Year Selection

AKath_12 — Thu, 14 Sep 2023 04:17:04 GMT

Hi, I wanted to obtain the average YoY changes based on the year selection, however, the average function only accepts a column reference as an argument (the error displayed).

1st Column:

Average Score this year =

CALCULATE(

AVERAGE(' Scores by Questions'[Average Scores])

)

2nd Column:

Average Scores Last Year =

CALCULATE(

AVERAGE('Scores by Questions'[Average Scores]),

DATEADD('All '[Start Date].[Date],-1,YEAR)

)

3rd Column:

Scores Changes YoY = if(ISBLANK([Average Scores Last Year]),BLANK(), if(ISBLANK([Average Score this year]),BLANK(),

[Average Score this year]-[Average Scores Last Year]))

The visualization that I put up as per below:-

Now I want to add one text statement with the value to display the Average Scores changes based on the year selection. The formula should be =Average([Scores Changes YoY]/'All '[Start Date].[Year]

For example:

If select Year 2022, then calculate (0.38-0.47+0.01)/3 years = -0.027

if select Year 2021, then calculate (0.38-0.47)/2 years = -0.04

My question is:

i) How do I compute the Dax formula correctly to display the correct Average Scores changes data based on year selection?

ii) I separate the Dax formula into different 3-4 columns due to not being very well versed in this, how do I combine/compute into one Dax formula?

Thank you very much for your help!

Re: Average YoY changes by Year Selection

123abc — Fri, 15 Sep 2023 10:43:43 GMT

To calculate the average year-over-year (YoY) changes based on the year selection in Power BI and display it as a single DAX formula, you can use the following approach. You'll need to create a measure that calculates the YoY change for each year, and then another measure to calculate the average of these YoY changes. Here's how you can do it:

Step 1: Create a Measure for YoY Changes

First, create a measure that calculates the YoY change for each year. You can use the following DAX formula for this measure:

```DAX
YoY Change =
VAR SelectedYear = MAX('All '[Start Date].[Year])
VAR PreviousYear = SelectedYear - 1
RETURN
CALCULATE(
SUM('Scores by Questions'[Scores Changes YoY]),
'All '[Start Date].[Year] = SelectedYear
) - CALCULATE(
SUM('Scores by Questions'[Scores Changes YoY]),
'All '[Start Date].[Year] = PreviousYear
)
```

This measure calculates the difference between the sum of "Scores Changes YoY" for the selected year and the previous year.

Step 2: Create a Measure for Average YoY Changes

Now, create a measure to calculate the average YoY change based on the year selection. You can use the following DAX formula for this measure:

```DAX
Average YoY Change =
VAR SelectedYear = MAX('All '[Start Date].[Year])
VAR TotalYears = COUNTROWS(ALL('All '[Start Date].[Year]))
RETURN
DIVIDE(
SUMX(
VALUES('All '[Start Date].[Year]),
[YoY Change]
),
TotalYears
)
```

This measure calculates the sum of YoY changes for all years and then divides it by the total number of years.

Step 3: Display the Average YoY Change

Now, you can use the "Average YoY Change" measure in your visualization to display the average YoY change based on the selected year. When you select a specific year in your slicer or filter, this measure will dynamically calculate and display the correct average YoY change.

By following these steps, you can compute the average YoY changes as a single DAX formula and display it based on the year selection in your Power BI report.

topic Re: Average YoY changes by Year Selection in DAX Commands and Tips

Average YoY changes by Year Selection

Re: Average YoY changes by Year Selection