Daily Average

[JamesBurke]

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 Name	Today's Usages	Daily Average total	Conditonal Formatting
Monday	10	9	Red
Friday	14	17	Green
Sunday	2	5	Green

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. 

[DataInsights · Accepted solution]

@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

[JamesBurke]

@DataInsights 

Thankyou !