Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Aggregation of minute data

I'm just getting to grips wiht Power BI, so this may seem a really simple question ...

I have 24 hours worth of energy consumption data at 1 minute resolution for a group of 7 triallists that I need to visualise

I have worked out how to split the date/time stamp and use bins to aggregate the time into 48 half hours

I also understand how to filter to enable presentation of individual triallists consumption or total for the group

How can I get the mean energy consumption for each triallist for each half hour period?

And then sum these to give the mean consumption for the group for each half hour?

I've been looking at groups and bins but not solved it

pbix file uploaded to https://we.tl/t-Ba4x3ufxdl

Thanks

Need Help
  • littlemojopuppy's avatar
    littlemojopuppy
    5 years ago

    Hi Anonymous 

    Try this...

    Measure = 
    VAR PowerUsageSummary =
        SUMMARIZE(
            'November 2020 one minute per device by triallist pbi',
            'November 2020 one minute per device by triallist pbi'[trialID],
            'November 2020 one minute per device by triallist pbi'[Time (bins)],
            "AverageConsumption",
            AVERAGE('November 2020 one minute per device by triallist pbi'[meanPowerKw]),
            "TotalConsumption",
            SUM('November 2020 one minute per device by triallist pbi'[meanPowerKw])
        )
    RETURN

    IF(
        ISFILTERED('November 2020 one minute per device by triallist pbi'[trialID]),
        AVERAGE('November 2020 one minute per device by triallist pbi'[meanPowerKw]),
        CALCULATE(
            SUMX(
                PowerUsageSummary,
                [AverageConsumption]
            )
        )
    )

     

1 Reply

  • littlemojopuppy's avatar
    littlemojopuppy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous 

    Try this...

    Measure = 
    VAR PowerUsageSummary =
        SUMMARIZE(
            'November 2020 one minute per device by triallist pbi',
            'November 2020 one minute per device by triallist pbi'[trialID],
            'November 2020 one minute per device by triallist pbi'[Time (bins)],
            "AverageConsumption",
            AVERAGE('November 2020 one minute per device by triallist pbi'[meanPowerKw]),
            "TotalConsumption",
            SUM('November 2020 one minute per device by triallist pbi'[meanPowerKw])
        )
    RETURN

    IF(
        ISFILTERED('November 2020 one minute per device by triallist pbi'[trialID]),
        AVERAGE('November 2020 one minute per device by triallist pbi'[meanPowerKw]),
        CALCULATE(
            SUMX(
                PowerUsageSummary,
                [AverageConsumption]
            )
        )
    )