Forum Discussion

bilogin's avatar
bilogin
Icon for Helper I rankHelper I
6 years ago
Solved

Percentage passed SLA

Hi all,

 

Thanks in advance for your help on this, I'm a BI noob and it's taken me a few days to get this far! 

 

So through Excel and our CRM, there is a bar and line chart that shows the volume of contact with a customer per month. 

 

There is an SLA to file this contact within 5 days and the bars need to show the % passed SLA. I've managed to create a new column that shows whether it passed or failed but I can't seem to get this information in BI. 

 
Need Help
  • tex628's avatar
    tex628
    6 years ago

    I'm assuming that the values displayed in the last image is the "Passing" percentage. In that case create a measure like this:

    Measure =
DIVIDE(
CALCULATE( COUNTROWS(ContactReport) ; ContactReport[Pass/Fail] = "Pass") ;
COUNTROWS(ContactReport) ;
BLANK()
)


    Format the measure as % and then place it in your chart and remove the legend. There should only be the measure and the date dimension. 

    If you dont get the correct result post a picture and we'll take another look 🙂 

    Br,
    J

13 Replies

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    6 years ago

    It's very difficult to provide help with the information provided. Pictures of current progress and expected outcome would help a lot. 

    Am i understanding you correctly that you have managed to display passed/failed SLA's in a barchart in PowerBI?

    Now you want to display it by % in a line chart and a bar chart?


    Br,
    J

    • bilogin's avatar
      bilogin
      Icon for Helper I rankHelper I
      6 years ago

      tex628 I did try to upload photographs but I'll try again. 

       

      So here is what I've added into BI so far (in case I have to remove the screenshot):

       

      DurationPassMonthFail
      1.533954PASSJul-2018 
      24.53 Jul-2018FAIL
      54.06 Jun-2018FAIL
      0.28PASSAug-2018 
      144.13373 Mar-2018FAIL

       

      I should be able to put the information into the graph, it's just the formula I'm having difficulty with.

       

      I've created another column with the formula: 

       
      = if('filterednewcontactreport'[month]="Aug-18",countrows('Filterednew_contactreport'[pass]="Yes"),"0")
       
      The formula is coming up with the following error: "A single value for column 'pass' in table 'Filterednew_contactreport' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
       
      I've just realised that it won't work as it will only count the passes and not the fails as it is in a different column. Gah!!!!
       
      What I'm looking for is to count up the passes and the fails for each month and calculate a % pass rate. 
       
      Hopefully that helps a little more! 

       

    • bilogin's avatar
      bilogin
      Icon for Helper I rankHelper I
      6 years ago

      tex628 I did submit photos but there was an invalid HTML?

       

      Basically, I would like to know the % pass rate for each month.

       

      This is what I've set up so far (in case the screenshot fails again):

       

       

      DurationPassMonthFailPass/Fail
      1.5642PassJul-2018 Pass
      57.0657 Jun-2018FailFail
      0.564PassJun-2018 Pass

       

       

      I did try:

      if([MONTH]="Jul-2018"CALCULATE(COUNTROWS(ContactReport);ContactReport[Pass] = "Pass",0) or something similar but I realised that I would miss out those that failed so I added a pass/fail column. Plus it didn't work anyway, there was a problem somewhere with the syntax.

       

      I tried to create a new table to duplicate what is in the old Excel file with the columns: Date/Yes/No/Percentage but I couldn't work out the formula

       

      I also tried to add a measure rather than a column but the issue with the syntax was solved online by changing from a measure to a column which didn't work for me. 

       

      Thanks,

      Bec

      • tex628's avatar
        tex628
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Alright,

        Create a "100% stacked column chart" and then add the following:

        Axis: [Month]
        Legend: [Pass/Fail]
        Value: Count of [Pass/Fail]

        This should show you the exact percentage of fails/passes related to each month. 

        Br,
        J