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 - 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. 

  • mark_endicott's avatar
    mark_endicott
    Icon for Super User rankSuper User
    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. 

     

     

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

      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's avatar
        mark_endicott
        Icon for Super User rankSuper User
        2 years ago

        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.