Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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
Solved! Go to 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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsGood 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.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Thank you for your time and help.
I tried using the formula - here it is
When I am using the below formula for simply calculating the totals I am having the correct totals
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsEverything is fixed now and perfect see below:
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:
But glad you could figure a way out.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCheck out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
100 | |
65 | |
44 | |
36 | |
36 |