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]),[...
  • 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. 

     

     

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. 

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

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

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

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

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

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