Question on measures and calculations in regards to change failure rate

[JohnFabric]

Hello, I have a PowerBI report that lists our IT changes, and if they were successful or not over the past twelve months

This is great and works well, but I also want to calculate the change failure rate on a month to month basis. That calculcation is

Change Failure for a month = SUM(Closed - Backout for a month, Closed - Issues for a month)/Total(all items)

so for August 2023 above it would be 

(2+11)/(2+11+324) = 3.8%

But I cannot figure out how to do that in PowerBI. The API that gives me this information returns items on a per change basis:

So when I go to add a measure to calculate this it doesn't offer me an option that would allow me to calculate it per month

How can I calculate the per month failure rate?

Thank you!

[Anonymous · Accepted solution]

Hi @JohnFabric ,

Based on your description, would you like to calculate the rate of Backout and Issues in a month relative to all status in the month? If yes, you can refer to my test below.

Create a measure.

Rate = COUNTROWS( FILTER( ALL( 'Table'),(Table[Status]="Backout"||[Status]="Issues")&&MONTH([Date])=MONTH( MAX('Table'[Date])) ))/COUNTROWS( FILTER(ALL( 'Table'),MONTH([Date])=MONTH( MAX('Table'[Date]))))

My table.

The result of my test.

You can hide other two “Rate” columns.

Best regards,

Mengmeng Li

[JohnFabric]

Thank you! That is what I am looking for and it gets me close with my data.

Here is what my table is (I've been tranforming some of the data)

And here is my measure

Rate = COUNTROWS( FILTER( ALL( 'Change Failure Rate'),('Change Failure Rate'[result.state]="Change Failure Rate")&&MONTH([result.closed_at].[MonthNo])=MONTH( MAX('Change Failure Rate'[result.closed_at].[MonthNo])) ))/COUNTROWS( FILTER(ALL( 'Change Failure Rate'),MONTH([result.closed_at].[MonthNo])=MONTH( MAX('Change Failure Rate'[result.closed_at].[MonthNo]))))

And now my graph looks like this

It seems to be some issue with the years, because I only have data going back 12 calendar months, so there is no data for pre-August 2023. Additionally, the data is being calculated out to exactly the same rate for all months, even though the rate varies month to month. Any idea on how I could fix this?

[JohnFabric]

Okay! I got this working, my issue was the months were being double counted because I was attemping to turn a MonthNo into a Month, rather then comparing the values directly via datetime (which the data is in)

So my new measure is

Rate = COUNTROWS( FILTER( ALL( 'Change Failure Rate'),('Change Failure Rate'[result.state]="Change Failure Rate")&&[result.closed_at].[MonthNo]=(MAX('Change Failure Rate'[result.closed_at].[MonthNo]))&&[result.closed_at].[Year]=(MAX('Change Failure Rate'[result.closed_at].[Year] ))))/COUNTROWS( FILTER(ALL( 'Change Failure Rate'),[result.closed_at].[MonthNo]=MAX('Change Failure Rate'[result.closed_at].[MonthNo])&&[result.closed_at].[Year]=(MAX('Change Failure Rate'[result.closed_at].[Year]))))

And that gives me the correct data back

Thank you for your help!

[JohnFabric]

Hello @Anonymous , I am trying to enhance this measure now and am not sure how to. I have modified my original query to include the area the change was introduced in

Values are redacted for privacy, but its stuff like "Azure server upgrade" and "Firewall change".  I then want to filter on my graph by this value

So I can basically say "what was the change failure rate for all firewall changes" instead of all changes. However since my graph is based on a measurement, the measurement doesnt change when I filter on 'CMDB name'. Do you know how I could get it to display the Change Failure Rate of different CMDB names?

[ryan_mayu]

could you pls provide some sample data and the expected output based on that?

[JohnFabric]

The data comes back from the API like this

I can then summerize this data with a graph like this:

Which gives me the below information. The expected output I want to generate is the new column at the end, Change failure rate for that month

Month	Closed - Backout	Closed - Issues	Closed - Successful	Change Failure Rate for the month (This is what I'm trying to calculate)
August	2	11	324	3.8%
Steptember	4	14	305	5.5%