Need Measure to Average results of another Measure

[mbailey]

Hi,

I have a Measure, MTBF = IFERROR([NumDaysInDateRange]/[NumSROs],BLANK()) that calcs the MTBF for every Unit,  per Partner, within a give date range:

Now, I need to make a measure, or some other method, that will return the Average MTBF per Partner within the select Date range as well, but I have no idea how to accomplish this. These results will be on a separate tab and be in the form of a table with the Partner's name and their Average MTBF.

Suggestions?

Thanks,

Mike

[parry2k]

@mbailey try this measure

AVERAGEX ( VALUES ( Table[Partner ), [MTBF Measure] )

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[mbailey]

Let me try presenting this again. My source data table would look like this below - Multiple Units assigned to Multiple Partners and the number of days (minus weekends and holidays) determined by a Date slicer. The number of Days in the Range will of course change as the Date range is changed.

My Measures are these: 

MTBF = ([NumDaysInDateRange]/[NumSROs])

NumSROs = DISTINCTCOUNT(Service_Report[sro_num])

NumDaysInDateRange = COUNT('DMTA_CalendarTable'[Date])

I need to create another Visual table that will show the Average MTBF for each Partner within the same Date range. It would look like this:

Any suggestions?

Thanks,

Mike Bailey

[AlexisOlson · Accepted solution]

Does what I suggested not work for averaging over units?

AvgPerUnit = AVERAGEX ( VALUES ( Table1[Unit] ), [MTBF] )

This should give you the values in your second table.

I'm not sure how meaningful these numbers are. I'd probably use a weighted average like this:

DISTINCTCOUNT ( Service_Report[Unit] ) * [NumDaysInDateRange] / [NumSROs]

[mbailey]

It's the average per Partner as I explained in my post. In the table shown, there are multiple Units per Partner, and I'm calcing the MTBF using the formula above for each Unit per Partner. So, if Parnert Mike had MTBF's of 128, 114, 85, and 92, I'd want to return 104.75 for his Average MTBF.  The thing is, that depending on the date range selected in a Date slicer, the number of Units will change, so I need to always calc the average using the MTBF's shown in the filtered table above.

[AlexisOlson]

It's not clear if you want average per unit or average per customer or something else but the general pattern is like this

AvgPerField = AVERAGEX ( VALUES ( Table1[Field] ), [MTBF] )

[parry2k]

@mbailey average MTBF by what?