Forum Discussion

PaulMac's avatar
PaulMac
Icon for Helper IV rankHelper IV
8 years ago
Solved

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. :smileyfrustrated:

---

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 :smileyhappy:

 

Need Help
  • Stachu's avatar
    Stachu
    8 years ago

    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 

2 Replies

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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 

    • PaulMac's avatar
      PaulMac
      Icon for Helper IV rankHelper IV
      8 years ago

      Many thanks Stachu that worked perfectly!! :smileyvery-happy:

       

      Kind regards

       

      Paul