Calculate reference average line using DAX

[DataVitalizer]

Hi Community,

I am trying to calculate the refence line average using DAX instead of enabling the line in the chart line visual as below

I create a measure with this code but it looks it does not return the same value as the auto refrence line

Reference AVG= DIVIDE ( CALCULATE (SUM(MyTable[Sales]),ALL(MyTable)), CALCULATE (DISTINCTCOUNT(MyTable[Date].[Quarter]), ALL(MyTable)))

Any suggestions?

Thank you in advance. 

[OwenAuger · Accepted solution]

Hi @DataVitalizer 

With your model as it is, you can try a measure like this:

Reference AVG = 
CALCULATE (
    AVERAGEX (
        SUMMARIZE ( MyTable, MyTable[Date].[Year], MyTable[Date].[Quarter] ),
        CALCULATE ( SUM ( MyTable[Sales] ) )
    ),
    ALLSELECTED ()
)

If you look at the DAX query's expression for the average line using Performance Analyzer, it is a little bit different because it executes a separate query for the average line.

As a side note, I would recommend not using Auto date/time, and instead creating your own Date table.

Regards

[DataVitalizer]

Thank you @OwenAuger