Averagex is not working as expected.

[vally57]

Hello Experts,
 I am trying to create an average of a measure based on a column called Engagement_Name.
For this I have written dax as follows:

average = AVERAGEX(VALUES(Audit[ENGAGEMENT_NAME]),[Adequate_FYTD_Audit]). but some how I'm not getting expected result.

Here I'm calculating average of Adequate_FYTD_Audit measure based on ENGAGEMENT_NAME. So, i should get (10+7+7)/3=8.
Please help me where am I missing the dax.
please find the attched pbix file for reference.
https://www.dropbox.com/scl/fi/nmj1jfkxfq6xr0dsp044a/test07.pbix?rlkey=47nqc96apoji7o7ifv5qht3iv&st=...

TIA

[mark_endicott]

@vally57 - Try this:

VAR numerator = SUMX( 'table'[Fiscal Year], [Adequate_FYTD_Audit] )

VAR denominator = DISTINCTCOUNT( 'table'[Fiscal Year] )

RETURN
DIVIDE( numerator, denominator , 0 )

This measure will not work when placed in the rows of a table, but if you add it to a card, with the same filters, you will see the value you want. 

[vally57]

@mark_endicott I want to see in tabular visual,where this logic doe not works

[mark_endicott]

@vally57  - So you want to see 8 in every row?

[vally57]

@mark_endicott yes, based on the filters the average might change.but from the above visual it should show 8

[mark_endicott]

@vally57 - Ok, seems strange you wouldn't just include one extra card visual that shows the overall average is 8, not 8 for each fiscal year, but here's something that may work:

VAR numerator = CALCULATE(SUMX( 'table'[Fiscal Year], [Adequate_FYTD_Audit] ), REMOVEFILTERS( 'table'[Fiscal Year]))

VAR denominator = CALCILATE(DISTINCTCOUNT( 'table'[Fiscal Year] ), REMOVEFILTERS( 'table'[Fiscal Year]))

RETURN
DIVIDE( numerator, denominator , 0 )

Or Try this:

VAR numerator = CALCULATE(SUMX( 'table'[Fiscal Year], [Adequate_FYTD_Audit] ), ALLSELECTED( 'table'[Fiscal Year]))

VAR denominator = CALCILATE(DISTINCTCOUNT( 'table'[Fiscal Year] ), ALLSELECTED( 'table'[Fiscal Year]))

RETURN
DIVIDE( numerator, denominator , 0 )

The reason this will be tricky is you need to remove the filter context of each row to make the calculation, but also keep the filters that are affecting elsewhere. And I'm worried that the removal of filters will affect the calculation of [Adequate_FYTD_Audit]

If this works, please accept as the solution. 

[vally57]

@mark_endicott it is not working

[mark_endicott · Accepted solution]

@vally57 - You will not be able to visualise it in the table. but the below DAX works fine for your requirement:

VAR numerator = SUMX( VALUES( 'Date'[Fiscal Year] ), [Adequate_FYTD_Audit] )

VAR denominator = CALCULATE(DISTINCTCOUNT( 'Date'[Fiscal Year] ), FILTER( Audit, COUNT( Audit[ENGAGEMENT_NAME] ) > 0 ))

RETURN
DIVIDE( numerator, denominator , 0 )

Suggest you visualise the Average in a card, otherwise your DAX will need to be very complex, and I have just tried many different methods and none work. 

[vally57]

@mark_endicott i want to show the average in bar graph, and btw the average should be shown based on AuditSubSection in the table not by fiscal year

[mark_endicott]

@vally57 - My approach will work in a bar chart if you were to use the Engagement name as the axis, it will then calculate an average per engagement across the years. 

The reason I have made the DAX across years, is your formula (10+7+7)/3 = 8 is averaging 3 fiscal years across one Engagement Name. 

[bhanu_gautam]

@vally57 , First check your measure

Adequate_FYTD_Audit = SUM(Audit[Adequate_FYTD_Audit_Column])

Then update second measure as

Average_Adequate_FYTD_Audit =
AVERAGEX(
VALUES(Audit[ENGAGEMENT_NAME]),
CALCULATE([Adequate_FYTD_Audit])
)

[vally57]

@bhanu_gautam I tried the same, but it is not working.