Forum Discussion

user900's avatar
user900
Helper II
5 years ago

Using Average

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
Jan01170.00%
Feb11170.85%
Mar31171.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.

 

 

 

 

Need Help

5 Replies

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    5 years ago

    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's avatar
      user900
      Helper II
      5 years ago

      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's avatar
        v-angzheng-msft
        Community Support
        5 years ago

        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