Forum Discussion

vally57's avatar
vally57
Icon for Helper I rankHelper I
2 years ago
Solved

Averagex is not working as expected.

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=p2ab1t6o&dl=0

TIA

  • mark_endicott's avatar
    mark_endicott
    2 years ago

    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. 

     

     

11 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    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])
    )

  • mark_endicott's avatar
    mark_endicott
    Icon for Super User rankSuper User
    2 years ago

    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.