cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver II

## Average of measure, depending on selected values?

Hi,

I am attempting to calculate an average energy consumption per area in buildings, kWh/m2.

I have a unique lookup Building table with Address and Area columns, and a very large time series Data table with energy consumption per hour.

I first wrote a DAX equation [sum of enrgy consumption] / [sum of area], but this of course does not return the correct value.

I am attempting to do something like this pseudocode:

``````var sum = 0

forEach building selected in the dashboard by the customer
{
sum += energy consumption / area
}

return sum / number of buildings selected``````

How would this be possible?

Cheers, Mike

2 ACCEPTED SOLUTIONS
Super User

Hi Mike,

I assume [sum of enrgy consumption] / [sum of area] are existing measures. Then you may try

``````Average Consumption =
AVERAGEX (
CALCULATE ( DIVIDE ( [sum of enrgy consumption], [sum of area] )
))``````

Community Champion

Ok, in that case you want to compute the "average of averages", which is legit but pay attention that in this case you are loosing the different contribution of differtent areas to the final result (which is taken under account in the original measure I sent).
In what you want than the measure is:

Average Consumption (Not Weighted) =
AVERAGEX(
Buildings,
DIVIDE(CALCULATE(SUM(Consumptions[Consumption])), Buildings[Area])
)

10 REPLIES 10
Super User

Hi Mike,

I assume [sum of enrgy consumption] / [sum of area] are existing measures. Then you may try

``````Average Consumption =
AVERAGEX (
CALCULATE ( DIVIDE ( [sum of enrgy consumption], [sum of area] )
))``````

Resolver II

Hi @tamerj1

This does the trick. You and @SpartaBI managed to post different, but both working solutions to the problem. Thanks, both of you 🙂

Cheers, MIke

Community Champion

"Work" depends on what is the question needed answer..
Do you want average per building (like the psuedo code you wrote..) or average per hour per building..

Resolver II

For clarity, what I want to calculate is one scalar value, representing the average of energy consumption divided by area for all selected buildings,ie:

For three selected buildings:

A, consumption 1000, area 300

B, consumption 21000, area 2500

C, consumption 500, area 120

result = ( 1000 / 300 + 21000 / 2500 + 500 / 120 ) / 3

Community Champion

Ok, in that case you want to compute the "average of averages", which is legit but pay attention that in this case you are loosing the different contribution of differtent areas to the final result (which is taken under account in the original measure I sent).
In what you want than the measure is:

Average Consumption (Not Weighted) =
AVERAGEX(
Buildings,
DIVIDE(CALCULATE(SUM(Consumptions[Consumption])), Buildings[Area])
)

Community Champion

"Work" depends on what is the question. Maybe I miss understood what you are trying to achieve.
For average of total consumption per building , and a dimension table of the buildings, the simple measure will work.
For average of consumption per hour per building, than yes, it sould be a different measure, but the psuedo code you sent didn't reflect that.
Do you want the average for every building taking into account the differnet rows?
As in if building A has 3 rows in the per hour table where each row is 10, you want the number for that building to be 10?

Super User

Hi @h4tt3n

Glad to see this subject posted on the forum. I am an energy efficiency engineer myself.
the answer to your query is yes it is possible but the method depends on the shape of your data. Most probably simple SUMX will solve the problem. You lease share some sample data.

Resolver II

Hi @tamerj1

Nice to be able to discuss this with a colleague 🙂

Here is a screenshot of the PBI table model with relations. I have obfuscated some names to keep our customer anonymous. The Unit table contains our physical measuring devices, scattered across addresses. Hope this helps.

Cheers, Mike

Community Champion

Hey @h4tt3n ,
In case your buildings table contains one unique row per building and it is related to the consupmtion table with 1 to many relationship, a simple measure like what you tried should work:

Average Consumption = SUM(Consumptions[Consumption]) / SUM(Buildings[Area])

Resolver II

Hi @SpartaBI

This won't work, simply because SUM( consumption ) / SUM( area ) does not return the right value. It needs to be (in pseudocode) AVERAGE( FOREACH( cunsumption / area ) ).

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.