Aggregate hourly KPI and cost to daily with dax?

[simber]

How do I calculate the daily average when I have hourly values of a KPI and cost?

I have this table (which is a subset of a much larger table) below that I want to take the daily average of so I can plot the daily average KPI and the daily average cost in a scatter plot to see the correlation between the performance and the costs.

The KPI is binary in a hourly resolution but is followed up as an average on a larger timeframe.

Time	Day	KPI	COST
2022-08-01 00:00	2022-08-01	1	20
2022-08-01 01:00	2022-08-01	0	100
2022-08-01 02:00	2022-08-01	1	20
2022-08-01 03:00	2022-08-01	0	40
2022-08-01 04:00	2022-08-01	0	60
2022-08-01 05:00	2022-08-01	0	80
2022-08-01 06:00	2022-08-01	0	70
2022-08-01 07:00	2022-08-01	0	30
2022-08-01 08:00	2022-08-01	0	20
2022-08-01 09:00	2022-08-01	1	-40
2022-08-01 10:00	2022-08-01	0	60
2022-08-01 11:00	2022-08-01	0	43
2022-08-01 12:00	2022-08-01	0	48
2022-08-01 13:00	2022-08-01	0	60
2022-08-01 14:00	2022-08-01	1	-30
2022-08-01 15:00	2022-08-01	0	60
2022-08-01 16:00	2022-08-01	1	10
2022-08-01 17:00	2022-08-01	0	90
2022-08-01 18:00	2022-08-01	0	45
2022-08-01 19:00	2022-08-01	1	20
2022-08-01 20:00	2022-08-01	0	48
2022-08-01 21:00	2022-08-01	1	10
2022-08-01 22:00	2022-08-01	0	45
2022-08-01 23:00	2022-08-01	0	60
2022-08-02 00:00	2022-08-02	1	15
2022-08-02 01:00	2022-08-02	0	68
2022-08-02 02:00	2022-08-02	1	12
2022-08-02 03:00	2022-08-02	0	78
2022-08-02 04:00	2022-08-02	1	18

The resulting "table":

Day	KPI	COST
2022-08-01	0,291667	40,375
2022-08-02	0,6	38,2

I don't really want a new table, I would prefer if I have 2 measures which give me daily average values as my current report.

[v-rongtiep-msft · Accepted solution]

Hi @simber ,

I have created a simple sample, please refer to it to see if it helps you.

Create 2 measures.

average cost =
VAR _cost =
    CALCULATE (
        SUM ( 'Table'[COST] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Day] = SELECTEDVALUE ( 'Table'[Day] ) )
    )
VAR _day =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Day] ), ALLSELECTED ( 'Table' ) )
RETURN
    _cost / _day

average kpi =
VAR _kpi =
    CALCULATE (
        SUM ( 'Table'[KPI] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Day] = SELECTEDVALUE ( 'Table'[Day] ) )
    )
VAR _day =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Day] ), ALLSELECTED ( 'Table' ) )
RETURN
    _kpi / _day

If it still does not help, please provide more details with your desired output. 

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.