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

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