calculate average at site level and individual value at ID level in DAX

[WTAS80486]

There are 4 sites and many assets under each site.

This measure calculates MTBF(M) at asset level with result as blank if either numerator or denominator is 0

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

Now how should i calculate average at site level 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.

MTBF(M) is a measure not calculated column

Average in red space at site (Ex MOU) 

[tamerj1 · Accepted solution]

Hi @WTAS80486 
Please use

MTBF(M) =
AVERAGEX (
    CALCULATETABLE (
        VALUES ( 'MeterReading'[Asset] ),
        CROSSFILTER ( ReadingDate[Reading Date], MeterReading[readingdate], NONE )
    ),
    CALCULATE (
        IF (
            [Runhour(Rolling)] = 0
                || [COUNT(failures)Cummulative] = 0,
            BLANK (),
            DIVIDE ( [Runhour(Rolling)], [COUNT(failures)Cummulative] )
        )
    )
)

[tamerj1 · Accepted solution]

Hi @WTAS80486 
Please use

MTBF(M) =
AVERAGEX (
    CALCULATETABLE (
        VALUES ( 'MeterReading'[Asset] ),
        CROSSFILTER ( ReadingDate[Reading Date], MeterReading[readingdate], NONE )
    ),
    CALCULATE (
        IF (
            [Runhour(Rolling)] = 0
                || [COUNT(failures)Cummulative] = 0,
            BLANK (),
            DIVIDE ( [Runhour(Rolling)], [COUNT(failures)Cummulative] )
        )
    )
)

[WTAS80486]

@tamerj1 

Thank a lot for your help!

[WTAS80486]

@tamerj1 

How can the same concept  be applied for description fields

COUNT(Assets by site) =

SUMX (

    CALCULATETABLE (

        VALUES ( 'MeterReading'[Asset] ),

        CROSSFILTER ( MeterReading[readingdate], ReadingDate[Reading Date], NONE )

    ),

    IF (

        [Runhour(Rolling)] == BLANK ( ),

        BLANK ( ),

        COUNTROWS (

            CALCULATETABLE (

                VALUES ( 'MeterReading'[Asset] ),

                CROSSFILTER ( MeterReading[readingdate], ReadingDate[Reading Date], NONE )

            )

        )

    )

) 

since they are not pulling data for rows where runhours is blank although rolling hours is populated

The description fields like asset URl, desction , location are not pulling data for rows with runhours blank coming from meter reading table

Asset URL(Measure) = MAX(asset[Asset URL])

Asset URL =

IF ( ISINSCOPE ( MeterReading[Asset] ), [Asset URL(Measure)], BLANK () )

so that they are populated at asset level but not site level

should be blank at site (MOU) level 

[tamerj1]

Hi @WTAS80486 
Please try

=
MAXX (
    CALCULATETABLE (
        VALUES ( 'MeterReading'[Asset] ),
        CROSSFILTER ( MeterReading[readingdate], ReadingDate[Reading Date], NONE )
    ),
    IF (
        [Runhour(Rolling)] == BLANK (),
        BLANK (),
        CALCULATE (
            [Asset URL(Measure)],
            CROSSFILTER ( MeterReading[readingdate], ReadingDate[Reading Date], NONE )
        )
    )
)

[WTAS80486]

@tamerj1 

It does display all description fields at asset level but also displays at site. At site it needs to be blank

Here needs to be Blank

Earlier i was using this  measure

IF ( ISINSCOPE ( MeterReading[Asset] ),MAX(asset[Asset URL]), BLANK () ),

But since its not pulling by asset from meter reading something else needs to be used and incorporated in the measure

[tamerj1 · Accepted solution]

@WTAS80486 
Please use

Asset URl(M) =
IF (
    ISINSCOPE ( MeterReading[Asset] ),
    MAXX (
        CALCULATETABLE (
            VALUES ( 'MeterReading'[Asset] ),
            CROSSFILTER ( MeterReading[readingdate], ReadingDate[Reading Date], NONE )
        ),
        IF (
            [Runhour(Rolling)] == BLANK (),
            BLANK (),
            CALCULATE (
                [Asset URL(Measure)],
                CROSSFILTER ( MeterReading[readingdate], ReadingDate[Reading Date], NONE )
            )
        )
    )
)

[WTAS80486]

@tamerj1 

Thanks a lot!

[tamerj1]

@WTAS80486 
let's chat tomorrow morning

[Greg_Deckler]

@WTAS80486 This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also, MM3TR&R: https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

[WTAS80486]

@Greg_Deckler 

the problem with this is 

MTBF Avg per site =

VAR __table = SUMMARIZE(MeterReading,MeterReading[Asset],"__value",[MTBF(M)])

RETURN

IF(HASONEVALUE(MeterReading[Asset]),[MTBF(M)],SUMX(__table,[__value]))

that i do not want to use asset. I want to calculate the sum of my measute MTBF(M) and divide it by non blank counts of the same measure MTBF() to calculate average at site level