Calculating Daily Average from Hourly Data

[anblancopr]

Good evening. 

I'm trying to create a measure that will bring back a constant daily average in a table grouped by hour as shown in the image bellow. 

Im pulling the data from the following table 

So the constant daily average should be 0.81041 devided by the number of days selected in the filter that in this case is 2 days. 

My idea was to use the following DAX but it's bringing back the daily average for the perticular hour only 

Any help would be greatly appreciated. 

Thank you!!

[Anonymous]

Hi @anblancopr ,

Please create a calculated column first.

Date_2 = DATE(YEAR('Fact_SCADA_Data'[Date]),MONTH('Fact_SCADA_Data'[Date]),DAY('Fact_SCADA_Data'[Date]))

And then create this measure.

Diurnal_DailyAvg_Folw =
VAR _days =
    COUNTROWS ( ALLSELECTED ( 'Fact_SCADA_Data'[Date_2] ) )
VAR _total =
    CALCULATE (
        SUMX (
            SUMMARIZE (
                ALLSELECTED ( 'Fact_SCADA_Data' ),
                'Fact_SCADA_Data'[Date],
                "Total FLOW", SUM ( 'Fact_SCADA_Data'[readvalue] )
            ),
            [Total FLOW]
        ),
        FILTER (
            'DIM_Signals',
            'DIM_Signals'[Data Type] = "Flow RATE"
                && 'DIM_Signals'[ITD SQL Server Table] = "AMR DATA"
        )
    )
VAR _result =
    DIVIDE ( _total, _days )
RETURN
    _result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum