Forum Discussion

JamesBurke's avatar
JamesBurke
Helper III
2 years ago
Solved

Daily Average

Hi all , 

 

 

Daily Average all = CALCULATE( AVERAGEX(
    VALUES('Date'[Day ID]),
    [Total Kwh]
),ALL('Date'))

 

 

Above Is the Dax forumla i'm using as a average of all days and then comparing todays usages aginst it and using conditonal formatting in green and red to show above and beow average usages. 

 

I Have now realised saturday and sunday where there is always lower consumption will always be green , i'm looking at making a similar dax measure that compares each invidual day to their Daily average of all dates. 

 

So For exmaple:

 

Day NameToday's UsagesDaily Average total Conditonal Formatting
Monday 109Red
Friday 1417Green
Sunday25Green

 

I have the Dax Measure For the conditonal Formatting just looking for a way to Breakdown the Daily Average of all dates to be broken down by Day Name rather then being one flat Value across all dates. 

 

Thanks , James. 

 

 

General Comment
Need Help
Tutorial Requests
  • DataInsights's avatar
    DataInsights
    2 years ago

    JamesBurke,

     

    Try a measure like this. You could use ALLSELECTED instead of ALL if you want a date slicer/filter to apply to the calculation.

     

    Daily Average by Day Name =
VAR vTable =
    CALCULATETABLE (
        VALUES ( 'Date'[Date] ),
        ALL ( 'Date' ),
        'Date'[Day Name] = MAX ( 'Date'[Day Name] )
    )
VAR vResult =
    AVERAGEX ( vTable, [Total Kwh] )
RETURN
    vResult

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    2 years ago

    JamesBurke,

     

    Try a measure like this. You could use ALLSELECTED instead of ALL if you want a date slicer/filter to apply to the calculation.

     

    Daily Average by Day Name =
VAR vTable =
    CALCULATETABLE (
        VALUES ( 'Date'[Date] ),
        ALL ( 'Date' ),
        'Date'[Day Name] = MAX ( 'Date'[Day Name] )
    )
VAR vResult =
    AVERAGEX ( vTable, [Total Kwh] )
RETURN
    vResult