Average of measure, depending on selected values?

[h4tt3n]

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

[tamerj1 · Accepted solution]

Hi Mike,

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

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

[tamerj1 · Accepted solution]

Hi Mike,

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

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

[h4tt3n]

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

[SpartaBI]

"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..

[h4tt3n]

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

[SpartaBI · Accepted solution]

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])

)

[SpartaBI]

"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? 

[tamerj1]

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. 

[h4tt3n]

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

[SpartaBI]

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])

[h4tt3n]

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 ) ).