Forum Discussion

anblancopr's avatar
anblancopr
New Member
3 years ago

Calculating Daily Average from Hourly Data

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!!

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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