Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 =
# 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%.
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.
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.
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.
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
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 |
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.