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

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