Forum Discussion
Helper IAveragex is not working as expected.
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.
Helper Imark_endicott it is not working
Super Uservally57 - 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.