Forum Discussion

Gingerjeans88's avatar
Gingerjeans88
Helper IV
6 years ago

Help with measures

Hi all,

I have a list of Quotes, multiple rows per QuoteID as a new row is added every time the Quote moves to a new stage.

My overall visualisation requirement is to show the percentage of quotes, per month, that were less than or equal to the total SLA minutes of 1020. Plus the volume of quotes. So a column and line chart.
(I have a calculated column for the total minutes.)

Is my best option to add all quote IDs as values to the chart, then run a measure of the top counting how many were within the SLA minutes and then show that as a percentage? I’m struggling with how to do that in a measure, perhaps lack of practice with DAX and no appropriate quick measure option!

Table for ref as I am not authorised to share the pbix.

QuoteID name Total minutes Date
ABC123 200 Oct
DEF456 1400. Oct
GHI789 350. Oct

Total within SLA would be 2, so 66% within SLA and the volume (which I’ll display as the line) is 3.

Need Help

6 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    Try these measures

    Total Volume = COUNTROWS(Data)

    Number of quotes = DISTINCTCOUNT(Data[QuoteID])

    Number of quotes within SLA = CALCULATE([Number of quotes],Data[Total minutes]<=1020)

    Number of quotes within SLA = [Number of quotes within SLA]/[Number of quotes]

    Format the last measure as a %.

    Hope this helps.

    • Gingerjeans88's avatar
      Gingerjeans88
      Helper IV
      6 years ago

      Hi Ashish_Mathur ,

       

      Thank you SO much for your help. Presumably I apply them all onto the visualisation at once?

      Also I am now concerned that my Total Minutes calculated column in the dataset isn't correct, as it's totalling the minutes per row and some row are duplicates...so the count distinct will be skewed. 

       

      Would it be ok if I sent you a dummy PBIX to look at for me?

      • Gingerjeans88's avatar
        Gingerjeans88
        Helper IV
        6 years ago

        HI Ashish_Mathur ,

         

        I have given this some more thought. All I need to do is calculate the sum of the 'total minutes' per unique ID and show which quotes are less than or equal to the desired total minutes (1020). 

         

        In other words, show me each record only once in the chart, but only if the sum of the 'total minutes' value for each row with that unique ID is less than or equal to 1020. Help! Losing my mind haha!