Using Average

[user900]

I have two data sources. One includes Incident detail, the other system detail. I created a relationship between them as both have  system name and code identifiers. I have the following measures:

# of systems w. Incidents not meeting SLA = 

CALCULATE(COUNT('Mo_Inc'[sys code]),'Mo_Inc'[sys code]="Yes",'Mo_Inc'[RTO Met?]="False")

# of systems = CALCULATE(COUNT('Systems'[sys code]),'Systems'[sys code]="Yes")

% of systems w. Incidents not meeting SLA = 'Mo_Inc'[# of systems w. Incidents not meeting SLA]/Systems[# of systems]

Monthly results using my measure are:

Date	# of systems w. Incidents not meeting SLA	# of systems	% of systems w. Incidents not meeting SLA
Jan	0	117	0.00%
Feb	1	117	0.85%
Mar	3	117	1.71%

1) I need to create a visual to show the % of systems with incidents that do not meet the SLA compared to total systems. I have monthly data but my visual will show quaterly data points. 

In the sample data, the 3 month period of Jan, Feb, Mar, there is a total of 4 systems w incidents over SLA, but only 117 systems.  117 is the average number of systems in the 3 months of the quarter. My data point needs to be 2.56%.

• Do I need to change my measure to use an average of the # of systems for each of the quarters or something else? The # of systems does change periodically? If I change the # of systems measure, what would it look like?

2) The visual needs to drill down to show the system where the incident occurred (assuming I can add the field to the axis)

3) then the visual needs to drill down one more level to show the incident(s) for each system (again - assuming I can add the field to the axis)

Any help would be greatly appreciated. Thanks in advance.

[v-angzheng-msft]

Hi, @user900 

I don't fully understand your question, how do you get 2.56% (4/117=3.4%?).

not sure about your actual model and data, if you show it by quarter should the measure be something like below?

# of systems =
CALCULATE (
    COUNT ( 'Systems'[sys code] ),
    FILTER ( ALLEXCEPT ( 'table', [quater] ), 'Systems'[sys code] = "Yes" )
)

And in measure # of systems w. Incidents not meeting SLA, Are both filter conditions met at the same time like below?:

# of systems w. Incidents not meeting SLA =
CALCULATE (
    COUNT ( 'Mo_Inc'[sys code] ),
    'Mo_Inc'[sys code] = "Yes"
        && 'Mo_Inc'[RTO Met?] = "False"
)

If the sample data and desired output are available here, then it's clear what should be implemented.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[user900]

2.56% is the sum of monthly results (0% + 0.85% + 1.71%) because I've been asked to calculate the results monthly, but to create thresholds quarterly using the sum. I'm not sure I agree with this approach vs your suggestion of 4/117 = 3.41% but I'm losing my argument unless I have more rationale behind my reasoning of that's how it should be done.

[v-angzheng-msft]

Hi, @user900 

Could you share some example data and draw a simple picture to show your expected visual so that i may work out with a workaround?😀

Best Regards,
Community Support Team _ Zeon Zheng

[user900]

Here is a sample of data. It shows the monthly results and the quarterly results. The quarterly results are the data points on my visual. I need the monthly to create tolerance limits which are based on historical data.

Date	# of sys w. Inc not meeting SLA	# of systems	monthly % of systems w. Inc not meeting SLA	quarterly
19-Apr	1	118	0.0085
19-May	1	117	0.0085
19-Jun	4	117	0.0342	0.0512
19-Jul	4	117	0.0342
19-Aug	6	117	0.0513
19-Sep	1	117	0.0085	0.0940
19-Oct	1	116	0.0086
19-Nov	2	116	0.0172
19-Dec	2	116	0.0172	0.0430
20-Jan	3	116	0.0259
20-Feb	0	116	0.0000
20-Mar	1	116	0.0086	0.0345
20-Apr	0	117	0.0000
20-May	1	117	0.0085
21-Jun	2	117	0.0171	0.0256

[v-angzheng-msft]

Hi, @user900 

I have no relevant experience with this user case and am not sure I understand your needs, do you need to create a quarterly percentage accumulation, if so then try to create the following measure

_quarterly = CALCULATE(SUM('Table'[monthly % of systems w. Inc not meeting SLA]),DATESQTD('Table'[Date]))

.Result:

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.