Sanity Check - AverageX

DAX DAX Calculations
1

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(
ALLSELECTED(‘Dates V2’[Academic Year]),
[Take Rate]
),
‘Dates V2’[Academic Year] <> CurrentYear
)

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

Current Academic Year =
VAR CurrentYear =
MAXX (
FILTER ( ‘Dates V2’, ‘Dates V2’[Academic Year] <> “AY Definition Needed” ),
‘Dates V2’[Academic Year]
)
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?

2

Hi @JamesMWilliams

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.

Give DataMentor a try to help you will your solution to your problems.
thanks
Keith

3

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

Thanks
Ankit J

4

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(
>         ALLSELECTED('Dates V2'[Academic Year]), 
>         '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