Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
bimystic
Helper I
Helper I

How to calculate value for Creating SLA reports in Power BI

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:

SLA_Report.JPG

 

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 NOPRIORITYHAS BREACHED
INC1 Priority 2False
INC2Priority 1False
INC3  Priority 2True
INC4  Priority 3False
INC5  Priority 3False
INC6Priority 2False

 

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

 

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

9 REPLIES 9
Syndicate_Admin
Administrator
Administrator

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?

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.
Stack_Percents.JPG
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

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" )
)
 
Matrix.JPG
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 
 
Matrix_Percent.JPG
 
Wonder why the divide is not working.

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Everything is fixed now and perfect see below:

 

FinalBar.JPGFinal.JPG

I was formatting the incorrect measure

 

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

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.

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors