cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
david_MAS Frequent Visitor
Frequent Visitor

SUM of all values less than 0

Hi All,

 

I'm trying to create a calculated column that outputs the number of breaches ("Failures") based on a set of criteria within a service level agreement. I'm currently getting the correct figures for each indvidual column, but ideally I want to sum all 6 columns - but only where the value is less than 0.

In this context, less than 0 constitutes an SLA failure, so I want to get a picture of how many failures there have been overall.

I've got as far as the below and am now getting a 1 if there are *any* failures and a 0 if there are none based on the "||" part of the syntax.

I can't seem to get past this point (knowledge/skills gap!) so would be grateful for any steers:

SLA Failures = 
Calculate(
IF(
    SUM('DAPA_SLA_Performance 01 07 18 t'[Deadline - Pre-Assessment QA]) <0  ||
    SUM('DAPA_SLA_Performance 01 07 18 t'[Deadline - File Receipt]) <0  ||
    SUM('DAPA_SLA_Performance 01 07 18 t'[Deadline - File Receipt]) <0  ||
    SUM('DAPA_SLA_Performance 01 07 18 t'[Deadline - Findings]) <0  ||
    SUM('DAPA_SLA_Performance 01 07 18 t'[Deadline - Urg. Rem. Action]) <0  ||
    SUM('DAPA_SLA_Performance 01 07 18 t'[Deadline - Rep. Receipt]) <0  ||
    SUM('DAPA_SLA_Performance 01 07 18 t'[Deadline - Rep. Response]) <0 , 1, 0
)
) 
3 REPLIES 3
d_gosbell Established Member
Established Member

Re: SUM of all values less than 0

If this is a calculated column and not a measure and if you want to see a value of 3 if 3 of the columns are less than 0 then you could do the following:

 

SLA Failures = 
IF('DAPA_SLA_Performance 01 07 18 t'[Deadline - Pre-Assessment QA] < 0 ,1,0)  +
IF('DAPA_SLA_Performance 01 07 18 t'[Deadline - File Receipt] < 0 ,1, 0) +
IF('DAPA_SLA_Performance 01 07 18 t'[Deadline - File Receipt] < 0 ,1, 0) +  
IF('DAPA_SLA_Performance 01 07 18 t'[Deadline - Findings] < 0,1, 0) +
IF('DAPA_SLA_Performance 01 07 18 t'[Deadline - Urg. Rem. Action] < 0 ,1, 0) +
IF('DAPA_SLA_Performance 01 07 18 t'[Deadline - Rep. Receipt] < 0,1, 0) +
IF('DAPA_SLA_Performance 01 07 18 t'[Deadline - Rep. Response] < 0,1, 0) 

) 

 

Super User
Super User

Re: SUM of all values less than 0

Hi  @david_MAS 

Try the following for your calculated column. It will give you the number of failures (assuming a failure is equivalent to SLA < 0)

 

SLA Failures = 
    IF('DAPA_SLA_Performance 01 07 18 t'[Deadline - Pre-Assessment QA]) <0 , 1, 0) +
    IF('DAPA_SLA_Performance 01 07 18 t'[Deadline - File Receipt]) <0, 1, 0) + 
    ///etc. all the rest here following the same pattern 

 

or another version, based on that TRUE() is 1 and FALSE() is  0 when treated as numbers

 

 

SLA Failures = 
    ('DAPA_SLA_Performance 01 07 18 t'[Deadline - Pre-Assessment QA]) <0)* 1 +
    ('DAPA_SLA_Performance 01 07 18 t'[Deadline - File Receipt]) <0) * 1 + 
    ///etc. all the rest here following the same pattern 

 

 

 

 

 

Super User
Super User

Re: SUM of all values less than 0

Hi @david_MAS ,

 

Based on the information you provide is difficult to get the correct answer.

 

Please see this post regarding How to Get Your Question Answered Quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 (courtesy of @Greg_Deckler ).

 

Can you please post some data and expected result.

 

Regards,

MFelix 



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

Proud to be a Datanaut!