Monthly total with calculated member incorrect

For our incident management process I’m trying to calculate per month the total minutes that incidents went out of SLA. I do this by calculating for every priority the minutes exceeding SLA for incidents.
When I look at the table, the numbers are correct per day, but the monthly total only shows a value that is way too low. Also when I click total for the whole table it returns exactly that number (month), but it’s again too low. You can easily see from the table that there are multiple values that are higher than the total, so that calculation cannot be correct.

My total calculation is:
[Measures].[P1 Minutes > SLA]+[Measures].[P2 Minutes > SLA]+
[Measures].[P3 Minutes > SLA]+[Measures].[P4 Minutes > SLA]
I also tried it with SUM, but same result

Looking into it further I have found out that it calculated the AVERAGE of the values. So if there are 4 values for P1 out of SLA it will add them up and then divide it by 4. No idea why. Apparently it also happens when I drill down into individual values. Sometimes they consist of multiple incidents out of SLA per day, and then an average is returned also.

is it possible because I use the predefined average resolution working days to calculate the minutes out of SLA?

Well the average resolution workdays didn’t work out. It kept taking the average. So i checked it to

CASE WHEN ([Measures].[Time to resolution - Breached],[Priority].[P1. Critical]) > 0
THEN
(([Priority].[P1. Critical],[Measures].[Time to resolution Elapsed hours])-(1 * ([Measures].[Time to resolution - Breached],[Priority].[P1. Critical])))*60
END

and then do this per priority and added the total. This resulted in the correct values.

It would have been very nice If i could make a conditional case statement to generalize the calculation. For example Case when Priority = P1 -> calculate as such, when Priority = P2 do this.

1 Like