topic Re: Calculate Sum of a measure(not column) for all IDs in a category in DAX Commands and Tips

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

WTAS80486 — Sun, 04 Sep 2022 17:49:57 GMT

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

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

tamerj1 — Thu, 18 Aug 2022 19:24:56 GMT

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

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

WTAS80486 — Sun, 04 Sep 2022 17:49:39 GMT

This is not working

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

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

tamerj1 — Thu, 18 Aug 2022 20:23:05 GMT

WTAS80486

Please try

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

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

WTAS80486 — Thu, 18 Aug 2022 20:27:05 GMT

Thank you tamerj1

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

WTAS80486 — Mon, 22 Aug 2022 16:26:33 GMT

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)

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

tamerj1 — Mon, 22 Aug 2022 17:16:40 GMT

Hi WTAS80486

please try

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

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

WTAS80486 — Mon, 22 Aug 2022 17:31:26 GMT

tamerj1

Gives the same thing

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

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

tamerj1 — Mon, 22 Aug 2022 18:11:37 GMT

Ok let's try

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

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

WTAS80486 — Mon, 22 Aug 2022 18:26:07 GMT

tamerj1 Thanks so much. It is working.

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

WTAS80486 — Wed, 24 Aug 2022 14:17:16 GMT

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!

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

tamerj1 — Wed, 24 Aug 2022 14:34:37 GMT

Hi WTAS80486

please try

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

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

WTAS80486 — Wed, 24 Aug 2022 19:53:09 GMT

tamerj1 Thanks much!

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

WTAS80486 — Sun, 04 Sep 2022 17:53:17 GMT

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

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

tamerj1 — Thu, 01 Sep 2022 15:34:32 GMT

WTAS80486

Please clarify further perhaps with an example.

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

WTAS80486 — Thu, 01 Sep 2022 16:46:54 GMT

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

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

tamerj1 — Thu, 01 Sep 2022 17:47:50 GMT

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.

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

WTAS80486 — Thu, 01 Sep 2022 21:46:33 GMT

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

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

WTAS80486 — Thu, 01 Sep 2022 22:24:31 GMT

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