cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

Help with SUMX & IF

Hi All

 

I tried searching the forums for previous posts but all existing examples are so specific that none of the solutions were able to help me.

I am sure this is going to be a simple fix but I am having some beginers issues unfortunately. Smiley Frustrated

---

Basically, I work out our SLA for our phone team but when the team gets 0 calls the SLA is at 100%. This confuses some readers of my report and so I want to build a DAX measure that would say: 

 

IF(Phones[Offered]=>0, "N/A"

ELSE

(perform equation to work out SLA%)

SUMX(Phones,Phones[SL]*Phones[Offered])/sum(Phones[Offered])

Any help on this would be greatly appreciated.

 

Many thanks in advance.

 

Kind regards

Paul Smiley Happy

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Help with SUMX & IF

I assume the SLA should be calculated for Phones Offered >0, if it is as in your example it's easy to correct

SLA% =
IF (
    SUM ( Phones[Offered] ) > 0,
    DIVIDE (
        SUMX ( Phones, Phones[SL] * Phones[Offered] ),
        SUM ( Phones[Offered] )
    ),
    "N/A"
)

if this doesn't work can you paste here few rows of sample data from Phones table? should be anonymised 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

2 REPLIES 2
Highlighted
Super User II
Super User II

Re: Help with SUMX & IF

I assume the SLA should be calculated for Phones Offered >0, if it is as in your example it's easy to correct

SLA% =
IF (
    SUM ( Phones[Offered] ) > 0,
    DIVIDE (
        SUMX ( Phones, Phones[SL] * Phones[Offered] ),
        SUM ( Phones[Offered] )
    ),
    "N/A"
)

if this doesn't work can you paste here few rows of sample data from Phones table? should be anonymised 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

Highlighted
Advocate II
Advocate II

Re: Help with SUMX & IF

Many thanks @Stachu that worked perfectly!! Smiley Very Happy

 

Kind regards

 

Paul

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors