cancel
Showing results 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

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

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

2 ACCEPTED SOLUTIONS
Super User

Hi @WTAS80486

``````MTBF(M) =
AVERAGEX (
CALCULATETABLE (
),
CALCULATE (
IF (
[Runhour(Rolling)] = 0
|| [COUNT(failures)Cummulative] = 0,
BLANK (),
DIVIDE ( [Runhour(Rolling)], [COUNT(failures)Cummulative] )
)
)
)``````
Super User

@WTAS80486

``````Asset URl(M) =
IF (
MAXX (
CALCULATETABLE (
),
IF (
[Runhour(Rolling)] == BLANK (),
BLANK (),
CALCULATE (
[Asset URL(Measure)],
)
)
)
)``````
10 REPLIES 10
Super User

Hi @WTAS80486

``````MTBF(M) =
AVERAGEX (
CALCULATETABLE (
),
CALCULATE (
IF (
[Runhour(Rolling)] = 0
|| [COUNT(failures)Cummulative] = 0,
BLANK (),
DIVIDE ( [Runhour(Rolling)], [COUNT(failures)Cummulative] )
)
)
)``````
Helper IV

Thank a lot for your help!

Helper IV

How can the same concept  be applied for description fields

COUNT(Assets by site) =
SUMX (
CALCULATETABLE (
),
IF (
[Runhour(Rolling)] == BLANK ( ),
BLANK ( ),
COUNTROWS (
CALCULATETABLE (
)
)
)
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

Super User

Hi @WTAS80486

``````=
MAXX (
CALCULATETABLE (
),
IF (
[Runhour(Rolling)] == BLANK (),
BLANK (),
CALCULATE (
[Asset URL(Measure)],
)
)
)``````
Helper IV

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

Super User

@WTAS80486

``````Asset URl(M) =
IF (
MAXX (
CALCULATETABLE (
),
IF (
[Runhour(Rolling)] == BLANK (),
BLANK (),
CALCULATE (
[Asset URL(Measure)],
)
)
)
)``````
Helper IV

Thanks a lot!

Super User

@WTAS80486
let's chat tomorrow morning

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper IV

the problem with this is

MTBF Avg per site =
RETURN

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

Announcements

#### 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 Monthly Update - June 2024

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

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors