Help with measures

[Gingerjeans88]

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.

[Ashish_Mathur]

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]

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?

[Ashish_Mathur]

Hi,

You are welcome.  My solution proposes the usage of measures only - no calculated columns at all.  Please retry.  If it still does not work, share the link from where i can download your PBI file.  In there, show me the exact result which you are expecting.

[Gingerjeans88]

Hi @Ashish_Mathur ,

Thanks again for your reply - unfortunately I am unable to link to my pbix as it contains personal data, but I have recreated sort of what the dataset looks like and will explain what I want. 

As you can see from the below, my Total Minutes is not useful as it totals per line, and I want to total minutes by QuoteID. 

So for QUO001 I want the value to be 1103. 

My column and line chart is to show the percentage of total (distinct, so that each line item isn't counted as a quote) quotes for a month that took less than or equal to 1020 minutes. Line value will be the total distinct quotes volume.

Per the below, I would expect to see only one item in the bar chart for November, as the 1103 exceeds the SLA, and for October I want to see two (not all six line items). 

How do I get a measure to count distinct rows, referencing the sum of my total minutes column??

QuoteID Name	Created On	Stage	Stage Start	Stage End	Stage 1 Duration (calculated, if stage equals 1)	Stage 2 Duration (calculated, if stage equals 2)	Stage 3 Duration (calculated, if stage equals 3)	Stage 4 Duration (calculated if stage equals 4)	Total Minutes (calculated, stage 1 + 2 + 3 + 4)
QUO001	20/11/2019	1	20/11/2019 11:00	20/11/2019 23:00	720				720
QUO001	20/11/2019	2	21/11/2019 08:00	21/11/2019 14:00		360			360
QUO001	20/11/2019	3	21/11/2019 14:02	21/11/2019 14:05			3		3
QUO001	20/11/2019	4	21/11/2019 14:10	21/11/2019 14:30				20	20
QUO002	01/11/2019	1	01/11/2019 10:00	01/11/2019 10:02	2				2
QUO002	01/11/2019	2	01/11/2019 10:10	01/11/2019 10:30		20			20
QUO003	30/10/2019	1	30/10/2019 09:05	31/10/2019 09:28	23				23
QUO003	30/10/2019	2	30/10/2019 09:30	30/10/2019 10:00		30			30
QUO003	30/10/2019	3	30/10/2019 10:10	30/10/2019 10:15			5		5
QUO003	30/10/2019	4	30/10/2019 10:23	30/10/2019 10:30				7	7
QUO005	12/10/2019	1	12/10/2019 13:30	12/10/2019 14:00	30				30
QUO005	12/10/2019	2	12/10/2019 14:10	12/10/2019 14:25		15			15

[Ashish_Mathur]

Hi,

I am still not clear with your requirement.  Only one column should show up for November because the total minutes exceed the SLA of 1020.  For October, nothing should show up because there is no Quote which exceeds the SLA of 1020.  You may download my PBI file from here.

Hope this helps.

[Gingerjeans88]

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!