Skip to main content
cancel
Showing results for 
Search instead 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

Reply
WTAS80486
Helper IV
Helper IV

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

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

 

Ankita80486_0-1662123923756.png

 

 

 

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

 

Ankita80486_1-1662123923763.png

 

 

2 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

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

View solution in original post

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

View solution in original post

10 REPLIES 10
tamerj1
Super User
Super User

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 

Thank a lot for your help!

@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

 

Ankita80486_2-1662478724933.png

should be blank at site (MOU) level 

Ankita80486_3-1662478949576.png

 

 

 

 

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

@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

Ankita80486_0-1662494539718.png

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

 

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

@tamerj1 

 

Thanks a lot!

@WTAS80486 
let's chat tomorrow morning

Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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

Helpful resources

Announcements
Europe Fabric Conference

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.