Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone,
I am looking for support on how to calculate SLA% when data need be filtered by status, statuses time sumed up and SLA calculated per type of activity.
My data looks like this:
Ticket | Status | Time | Type |
111 | unassigned | 1 | cooking |
111 | in-progress | 2 | cooking |
111 | Pending 3rd party | 3 | cooking |
111 | in-progress | 1 | cooking |
111 | draft | 1 | cooking |
222 | unassigned | 1 | cleaning |
222 | in-progress | 1 | cleaning |
222 | Pending 3rd party | 2 | cleaning |
333 | unassigned | 1 | cooking |
333 | Pending 3rd party | 2 | cooking |
333 | in-progress | 1 | cooking |
333 | Pending 3rd party | 1 | cooking |
333 | in-progress | 2 | cooking |
444 | unassigned | 1 | cooking |
444 | in-progress | 1 | cooking |
555 | unassigned | 1 | cleaning |
555 | in-progress | 1 | cleaning |
I need to filter items in status in-progress and unassigned, sum up time for these two statuses and calculate SLA per type of activity.
Type | Ticket | Sum of Time | InSLA |
cleaning | 222 | 2 | N |
555 | 2 | Y | |
cooking | 111 | 4 | N |
333 | 4 | N | |
444 | 2 | Y |
which gives
Type | SLA% |
cooking | 33% |
cleaning | 50% |
How do I do that?
Thanks!
EM_Z
Solved! Go to Solution.
Hi,
PBI file attached.
Hope this helps.
Hi @Ashish_Mathur ,
Thank you very much for providing the example!
I didn't manage to go back to my actual PBI before you closed this topic - apologies for that, however, I would have an additional question. How would the measure need to be changed in case of two different SLAs for cleaning and cooking example, let's say 2 and 3h respectively?
In my real business case example I placed in PowerQuery a formula that states what is SLA target time per each category (saying that the SLA target will be available per each item in the table).
I hopped to refer to it in a measure with no luck as a measure finds only other measures.
Then found in the forum the formula selectedvalue - used it in following way:
1) SLA= selectedvalue('My raw data'[SLA]) ) and refered in measure
2) My new measure = countrows(FILTER(VALUES('My raw data [Case Number]),[FILTERED_TIME]<=[SLA]])) then in
3) SLA% = DIVIDE([My new measure],[Total tickets])
here also without expected results.
Would you mind helping me here with example you provided to build calculation based on different SLA for these two categories?
Thank you in advance!
You are welcome. I did not close the post. You are replying after over 20 days. I do not remember anything the question or my solution. May be someone else can help you.
To calculate the SLA% based on the filtered status and summed time per type of activity using Power BI, follow these steps:
1. Import your data into Power BI. You can enter the data manually in Power BI or load it from an external source like Excel.
2. Create a calculated column to filter and sum the time for the statuses 'in-progress' and 'unassigned'. Use a DAX formula like the following:
Filtered Time =
CALCULATE(
SUM('Table'[Time]),
'Table'[Status] IN {"unassigned", "in-progress"}
)
3. Create a measure to calculate the SLA per type of activity. The SLA could be defined as the percentage of tickets that meet a certain condition. For instance, if "InSLA" means that the sum of time for 'unassigned' and 'in-progress' is less than or equal to 3, then use the following DAX formula:
SLA Measure =
VAR TotalTickets = COUNTROWS('Table')
VAR InSLATickets = CALCULATE(
COUNTROWS('Table'),
FILTER(
SUMMARIZE('Table', 'Table'[Ticket], 'Table'[Type], "SumTime", [Filtered Time]),
[SumTime] <= 3
)
)
RETURN
DIVIDE(InSLATickets, TotalTickets)
4. To see the SLA% by activity type, you might need to adjust the context of your calculation using a visual level filter or by adjusting the DAX formula to group by the type of activity. For example:
SLA% by Type =
CALCULATE(
[SLA Measure],
ALLEXCEPT('Table', 'Table'[Type])
)
5. Create a report with a table visual that groups data by 'Type' and includes the 'SLA% by Type' measure. This will give you the SLA% per activity type, as requested.
6. Additionally, to display details per ticket as in your example, create a matrix visual with 'Type' and 'Ticket' as rows, 'Sum of Time' as values, and 'InSLA' as another value calculated by a similar approach.
Here's a DAX for the 'InSLA' column:
InSLA = IF([Filtered Time] > 3, "N", "Y")
By following these steps, you should be able to visualize and calculate the SLA% per type based on the filtered status and summed times as described. Remember to adjust the logic of 'InSLA' and SLA thresholds as per your specific requirements.
If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
Thank you @AnalyticsWizard .
Following your instructions I faced below issue. PBI does allow me to pick time calculated in step 2. What could be the reason?
Hi Jing,
Thank you for your answer.
<=2h in SLA, >2h not in SLA.
*with that SLA for cleaning would be 100%, apologies for this mistake
I created simlar formula, which seems to work.
but now coming back to my biggest issue right now, how to calculate SLA %? SLA needs to be calculated per ticket and the per day/month etc. -> SLA% is count of items for sum processing time lower than 2h and devided by all tickets raised during a day.
I hopped that I will be able to refer to the 1st measure when creating 2nd measure (count Yes on tickets with status unassigned and devide by items with Yes and No with status unassiged) but it does not work - I assue the reason is returning Yes and No in the 1st measure? If my assumption is right, then how to move to SLA% calculation using your solution?
At the end graph would need to show daily SLA.
Thank you for your further help.
BR,
EM_Z
Hi @EM_Z
To calculate the sum of time by filtering the status, you can create the following measure.
For the SLA calculation, can you explain how to decide whether it is Y or N? And to calculate the SLA%, should it divide the sum of "Y" by the total of each type?
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
User | Count |
---|---|
98 | |
91 | |
84 | |
71 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |