How to calculate value for Creating SLA reports in Power BI

[bimystic]

I am creating a Power BI report to show the SLA KPI for my project. All I have as input is the extract of the incidents from Service Now

Here is the link to the excel sheet: https://1drv.ms/x/s!AvJ523skMP3lakQo78RNc402YEs?e=qb8pjh

I want to create the below reports for SLA display purpose:

So I have the raw data of all incidents till data and imported them in my Power BI project.

Report 1: Incident Resolution

Group by priority type and show SLA resolution breached or not in percent. There is a column in the field which is HasBreached - bool False/True which helps to decide if the incident resolution is ok or not

Report 2: Incident Mean Time to Resolve

Group by priority and for each priority calculate meantime which was taken to resolve them. I have time for all incidents and can take the average

Report 3: Incidents Run mode or flow

Total incidents in progress, resolved and in hold in the month

Report 3: Reopened Incident Count

Total incidents reopened - there is a filed in the excel having reopened count and if it greater than zero then need to sum all the incidents and divide by total incidents

All reports to be shown on monthly stats - that I am aware of if once the reports are created I will put a filter on Year and Month and the report data will be shown for the particular month. 

If you can suggest how(approach) I proceed with the calculation for the reports then it will be of great help

I am not able to figure out how to do the totals

e.g. for incidents which have not breached to calculate their totals from the raw data like below:

Raw data looks like this - you can also refer excel URL

INCIDENT NO	PRIORITY	HAS BREACHED
INC1	Priority 2	False
INC2	Priority 1	False
INC3	Priority 2	True
INC4	Priority 3	False
INC5	Priority 3	False
INC6	Priority 2	False

So based on the above 

Priority 1 -100%

Priority 2 - 66%

Priority 3 - 100%

Can you provide me with some lead so that I am jump start on it? I have imported the data and now trying to figure for the approach for this. Later I will create a dashboard. I am aware of the DAX and created one dashboard in the past. 

Your help will be highly appreciated

Regards

BI Mystic

[Syndicate_Admin]

Good morning, I have a column in which I give the value in text of whether the ticket met the resolution and I need to calculate from among those that was not fulfilled and the SLA was fulfilled, in excel I do so Example: 100% - (Does not meet / If Meets), how can I replicate this same in Powerbi?

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[MFelix · Accepted solution]

Hi @bimystic .

To calculate the percetanges you show you need to add a measure similar to the one below:

% Breached =
DIVIDE (
    CALCULATE ( COUNT ( Table[Incident] ); Table[HASBREACHED] = FALSE () );
    CALCULATE ( COUNT ( Table[Incident] ) )
)

This measure may need adjustments but is similar to this, then you just need to place the priority and when you add the measure to the visualization calculation will be made accordingly.

[bimystic]

Hi @MFelix 

Thank you for your time and help.

I tried using the formula - here it is 

PercentBreached =

DIVIDE (

CALCULATE ( COUNT ( TASK_SLA_IMPORT_SNOW[Incident_No] ), TASK_SLA_IMPORT_SNOW[Has_Breached] = "FALSE"),

CALCULATE ( COUNT ( TASK_SLA_IMPORT_SNOW[Incident_No] ) )

)

I used a stack column chart and then I added the PercentBreached Measure in Value and Priority in Axis. All the priority have the same value which is 1 and is incorrect.

Another strange part is that though the value of PercentBreached is 1 for each of them still the towers are of different height.

Where I am messing it up?

Regards

Bimystic

[bimystic]

When I am using the below formula for simply calculating the totals I am having the correct totals 

totalincidents =

(CALCULATE ( COUNT ( TASK_SLA_IMPORT_SNOW[Incident_No] ), TASK_SLA_IMPORT_SNOW[Has_Breached]="False" )

)

Then I add the divide in this formula and it again starts coming back to 1 and 0 

Here is the divide formula

totalpercentincidents =

DIVIDE( (CALCULATE ( COUNT ( TASK_SLA_IMPORT_SNOW[Incident_No] ), TASK_SLA_IMPORT_SNOW[Has_Breached]="False" )),

COUNT(TASK_SLA_IMPORT_SNOW[Incident_No]))

Here is the result which is incorrect 

Wonder why the divide is not working.

[MFelix]

Hi @bimystic ,

You need to format the measure to percentage. When you add to the visualizations it give you integer numbers so rounds to 0 and 1.

[bimystic]

Everything is fixed now and perfect see below:

I was formatting the incorrect measure

Thanks a lot for your help. Stay safe and blessed.

[bimystic]

It is now showing the data for each priority correctly, what I did is taken 3 measures

One measure to calculate the totalBreached and one measure for total incidents. The third measure is a simple divide of the 2 measures. This is showing the correct result now. Strange it does not work on the same measure. 

Still working on the formatting as I changed to percent however the matrix is still showing in decimals like .33, .89 - do I multiply by 100 in the measure.

Thanks, @MFelix as you helped me forward and showed the way. Now I am feeling a bit confident.

[MFelix]

Hi @bimystic ,

You don't need to do a 3 measure just format the measure see this post:

https://stackoverflow.com/questions/38353064/how-to-format-new-measure-not-column-in-power-bi-as-per....

But glad you could figure a way out.