Forum Discussion

Pbiuserr's avatar
Pbiuserr
Post Prodigy
4 years ago

SLA calculation shows incorrect values

Hello, 

 

EDIT: I've got root of my problem:

SLA is supposed to be resolved request right. I got plenty of resolved requests but their SLA column is not filled. If I assume that BLANK() is Breached, then it counts nicely, but when I do %SLA and divide it by number of occurencies in SLA column, the blanks are not going to be counted, therefore the denominator is less than suppose to be

-----


I got one table so there is no really datamodel as of yet. I want to calculate SLA, by that I got a column with "Breached" / "Not breached" values and some nulls unfortunately (treated as breached)

 

My formulas are:

#ofTickets = countrows(filter('data', 'data'[CREATED_AT])) - this gives same amount of rows than in column with 'data'[SLA]

#inSLA = countrows(filter('data', 'data'[SLA] = "Not breached"))

#not_in_SLA = countrows(filter('data', 'data'[SLA] = "Breached" || 'data'[SLA] = BLANK() ))

 

Out of that I try to calculate % of SLA and % of not in SLA

 

%ofSLA = [#inSLA] / [#ofTickets] (tried also CALCULATE(COUNT('data'[SLA]), ALL('data'[SLA]))

 

%ofNoSLA = [#not_in_SLA] / [#ofTickets] (tried also CALCULATE(COUNT('data'[SLA]), ALL('data'[SLA])

 

The values looks good, however when I cross them by for instance priority, the percentages goes completly off. Any suggestions? Maybe PBI when crosses in table doesnt count the null values? but whole numbers in table looks good

EDIT: Figured out the problem

Need Help

5 Replies

    • Pbiuserr's avatar
      Pbiuserr
      Post Prodigy
      4 years ago

      Hi,
      unfortunately it does not work as intended

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        Pbiuserr ,If this does not help
        Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    4 years ago

    Hi, Pbiuserr 

     

    Has your problem solved? I saw you haven't responded.

    I checked your formula, it's very simple, There should be no problem. If you don't want values to change with the context of your visuals, you should add 'all' in codes, like:  countrows(filter(all('data'),...))

     

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

    Best Regards,
    Community Support Team _ Janey

    • Pbiuserr's avatar
      Pbiuserr
      Post Prodigy
      4 years ago

      Hi v-janeyg-msft  and amitchandak 

      Sorry for late response. Yes everything was good at the very beginning it was problem from the data point of view. I figured it out some time after creating this thread that my calculations were right, just data provided has some flaws. Thank you again for your input and your willingness to help