Calculate Sum of a measure(not column) for all IDs in a category

[WTAS80486]

I need to calculate average reliability for each site which will be sum of (Reliability Old M)for all assets in a site(NDA and 3 other sites) divided by count of assets in a site. (Reliability Old M) is a measure and not column

below measure is not working

Reliability= DIVIDE(SUMX('MeterReading','MeterReading'[Reliability Old M]),'MeterReading'[COUNT(Assets by site M)])

[tamerj1 · Accepted solution]

@WTAS80486 

Please try

Reliability =
AVERAGEX (
    SUMMARIZE ( 'MeterReading', 'MeterReading'[Site], 'MeterReading'[Asset] ),
    [Reliability Old M]
)

[tamerj1 · Accepted solution]

Ok let's try

Reliability =
AVERAGEX (
    CROSSJOIN ( VALUES ( 'MeterReading'[Site] ), VALUES ( 'MeterReading'[Asset] ) ),
    COALESCE ( [Reliability Old M], 0 )
)

[tamerj1]

Hi @WTAS80486 

it depends where do you want to view this measure. Uou can use the following if you are slicing by Site. 

Reliability =
AVERAGEX ( VALUES ( 'MeterReading'[Site] ), [Reliability Old M] )

you can also edit the original measure in order to see the old value at the asset level and the average at the site level in the same matrix

[WTAS80486]

This is not working

It needs to be here for site, so summ of all reliability Old M / No of assets in this site

[tamerj1 · Accepted solution]

@WTAS80486 

Please try

Reliability =
AVERAGEX (
    SUMMARIZE ( 'MeterReading', 'MeterReading'[Site], 'MeterReading'[Asset] ),
    [Reliability Old M]
)

[WTAS80486]

@tamerj1 

In the below expression

Reliability =
AVERAGEX (
SUMMARIZE ( 'MeterReading', 'MeterReading'[Site], 'MeterReading'[Asset] ),
[Reliability Old M]

It is calculating average at the site level  but considering only those assets which have reliability value populated at asset level, whereas i wanted to include all assets in count(even the ones with blank reliability values)

[tamerj1]

Hi @WTAS80486 

please try

Reliability =
AVERAGEX (
    CROSSJOIN ( VALUES ( 'MeterReading'[Site] ), VALUES ( 'MeterReading'[Asset] ) ),
    [Reliability Old M]
)

[WTAS80486]

@tamerj1 

Gives the same thing

It is not taking all assets in account( excluding the ones with blank [Reliability Old M] value

[tamerj1 · Accepted solution]

Ok let's try

Reliability =
AVERAGEX (
    CROSSJOIN ( VALUES ( 'MeterReading'[Site] ), VALUES ( 'MeterReading'[Asset] ) ),
    COALESCE ( [Reliability Old M], 0 )
)

[WTAS80486]

@tamerj1 

In the expression

AVERAGEX (

    CROSSJOIN ( VALUES ( 'Asset'[Site] ), VALUES ( 'Asset'[Asset Number] ) ),

   COALESCE ([MTBF(M)], 0 )

)

This calculates value at asset level as well as average at site level, but at site level i wanted to take total of MTBF(M) and divide by total no of MTBF(M (including 0) and not divide by  total asssets

[tamerj1]

@WTAS80486 

Please clarify further perhaps with an example. 

[WTAS80486]

@tamerj1 

Ok let me reframe

I want to calculate MTBF at asset level= Rolling runhours/rolling failures

Condition is that if either of rolling runhours or rolling failures is 0 then MTBF should be blank, which is not happening now

Then at site level (space in red) i want to also calculate MTBF average=total MTBF by count of non blanks MTBF and not by count of asset in that site

[tamerj1]

@WTAS80486 

It is difficult to answer your question without having my eyes on the data and the data model. It would be really helpful if you can share a sample file that contains a reasonable amount of dummy data that simulates the actual data. 

[WTAS80486]

@tamerj1 

So this expression 

MTBF(M) = DIVIDE([Runhour(Rolling)],[Rolling Failures])

)

Calculates MTBF(M) column

But as you can see MTBF(M) is 0.00 when rolling runhours is 0.00

 and blank when failures 0

Desired result is --> if either of rolling runhours or rolling failures is 0 then MTBF(M) should be blank

Also after this calculation which is for each asset under site, an average at site level should be calculated  for space in red which will be = sum of MTBF(M)/COUNT of Non blanks MTBF(M) under that site (Ex MOU)

Now another measure 

MTBF(M) Avg per site =

AVERAGEX (

    CROSSJOIN ( VALUES ( 'MeterReading'[Site] ), VALUES ( 'MeterReading'[Asset] ) ),

  [MTBF(M)]

)

does calculate MTBF at asset and average at site level but considers assets and i want to consider non blank MTBF (M) values not assets

[WTAS80486]

@tamerj1 

So i got blank result if either rolling runhours or rolling failures is 0

MTBF(M) = if([Runhour(Rolling)]=0 || [Rolling failures]=0, blank(), DIVIDE([Runhour(Rolling)],[Rolling failures))

Now how should i calculate average of this MTBF(M)=sum of MTBF(M)/ non blank count of MTBF(M) so that at site level average is displayed but at assets level MTBF(M) individual value is displayed

[WTAS80486]

@tamerj1  Thanks so much. It is working.

[WTAS80486]

@tamerj1I am calculating average Mean at the site level  for all assets under that site

MTBF =

AVERAGEX (

    CROSSJOIN ( VALUES ( 'MeterReading'[Site] ), VALUES ( 'MeterReading'[Asset] ) ),

   COALESCE (Total, 0 )

)

Where Total= Hours/F for each asset under that site

Now i want to write an expression for calculation average of all MTBF for 4 sites to create a line average on chart which already has 4 bar graphs for 4 MTBF

This dashed line is average line in colum chart 

But i wanted to used line and column chart and use the average expression in Line y-axis

Could you please help!

[tamerj1 · Accepted solution]

Hi @WTAS80486 

please try

Average =
AVERAGEX ( ALLSELECTED ( 'MeterReading'[Site] ), [MTBF] )

[WTAS80486]

@tamerj1  Thanks much!

[WTAS80486]

Thank you @tamerj1