Forum Discussion

DataVitalizer's avatar
DataVitalizer
Super User
2 years ago
Solved

Calculate reference average line using DAX

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. 

Need Help
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    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

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    2 years ago

    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