# Sanity Check - AverageX

So I established the below formula to allow me to show the Average Course Take Rate based on the academic years selected, excluding the current academic year. This basically gives individuals a standard by which to compare the progression of the current academic year.

The calculation I am using is:

Average Take Rate =
VAR CurrentYear = [Current Academic Year]
RETURN
CALCULATE(
AVERAGEX(
[Take Rate]
),
)

Where [Current Academic Year] is a measure defined as:

VAR CurrentYear =
MAXX (
FILTER ( ‘Dates V2’, ‘Dates V2’[Academic Year] <> “AY Definition Needed” ),
)
RETURN
CurrentYear

Course Take Rate is defined as:

Take Rate = CALCULATE(
DIVIDE([Registrations], [Students Enrolled]), VALUES(‘Dates V2’[Academic Year]))

To check to make sure everything was working I looked at the Take Rate from the calculation in a visual next to the academic years. If I average all of those 3.85, 3.87, 4.03, and divide by 3, I get 3.92, but the Average X formula returns 3.85. What am I missing?

Using DataMentor/EDNA AI tool built within the EDNA platform. This might help.

``````How does the context transition work differently for AVERAGEX and AVERAGE functions in PowerBI?
``````

## Context Transition in Power BI

### `AVERAGE` Function

• Context: Operates in the context of the column specified.
• Behavior: No context transition occurs. The function calculates the average of the values in the specified column within the current filter context.
• Usage: Often used for straightforward column-based calculations where there’s no need for row-by-row evaluation.
``````-- Example: Average revenue for the current context
AVERAGE(Sales[Revenue])
``````

### `AVERAGEX` Function

• Context: Operates over a table with an expression evaluated for each row.
• Behavior: Context transition occurs because the expression inside `AVERAGEX` is evaluated row by row. During this evaluation, the row context is transformed into a filter context.
• Usage: Useful for more complex calculations where the row-based evaluation is necessary, such as when you need to perform arithmetic operations before averaging.
``````-- Example: Average profit for each row, where profit is Revenue minus Cost
AVERAGEX(Sales, Sales[Revenue] - Sales[Cost])
``````

### Details on Context Transition

1. Implicit Context Transition:
• `AVERAGE` does not implicitly create any additional context. It relies solely on the filter context applied externally.
• `AVERAGEX`, however, transitions the row context of each row being evaluated into a filter context to compute the expression.
1. Row Context:
• `AVERAGEX` brings in the current row context into play by evaluating the specified expression for each row, leading to a row-by-row filter application.
• `AVERAGE` lacks this row context and doesn’t perform such individual row-level calculations.

Overall, use `AVERAGEX` when you need to handle calculations that vary for each row, while `AVERAGE` is suitable for straightforward aggregation of single-column data.

thanks
Keith

Hi @JamesMWilliams - Do share the PBIX file to check the issue.

Thanks
Ankit J

I was able to figure it out. I wish I could share the PBIX files as I am sure it would make life a lot easier but the university prohibits that.

This is what I found to work:

``````> Average Take Rate =
> VAR CurrentYear = [Current Academic Year]
> VAR FilteredYears =
>     FILTER(
>         'Dates V2'[Academic Year] <> CurrentYear
>     )
> RETURN
> CALCULATE(
>     AVERAGEX(
>         FilteredYears,
>         [Take Rate]
>     )
> )
``````

where [Take Rate] is:

``````Take Rate = CALCULATE(

DIVIDE([Registrations], [Students Enrolled]), VALUES('Dates V2'[Academic Year]))
``````
1 Like