SLA Calculations by Week Region and location

[VaishnaviGandhi]

Hi Everyone,

I have a case where I am required to show SLA Met based on a filter of week, region and location my data is represented like below:

And I need a return data in a seperate table or same table as per below based on per week per region and per locations SLA met - Yes count and total based on the same

Question to be answered: SLA met by week,by region & by location 

The ultimate outcome is getting to SLA % which I am aware how to get but transformin the data to the table 2 in power bi is were i am strugling.

Currently I am using the below formula to get the Sla met by region 

Region Yes = CALCULATE(COUNTROWS('Week IT SLA Data'),FILTER(ALL('Week IT SLA Data'),'Week IT SLA Data'[SLA Met] ="Yes" && 'Week IT SLA Data'[Region] = EARLIER('Week IT SLA Data'[Region])))

Currently I am using the below formula to get the Sla met by week

[SLA Met Total ] = CALCULATE(COUNTROWS('Weekly Data'),FILTER(ALL('Weekly Data'),'Weekly Data'[SLA Met] = "Yes" && 'Weekly Data'[Week] = EARLIER('Weekly Data'[Week])))

Note: these 2 formulas are being used seperatly need a solution on combining SLA MET by region, by week number and by location. 

Thank you

Vaishnavi 

[Anonymous · Accepted solution]

Hi @VaishnaviGandhi 

You can create a new table

Table 2 =
SUMMARIZE (
    'Table',
    [Week],
    'Table'[Location],
    [Region],
    "Yes",
        CALCULATE (
            COUNTROWS ( 'Table' ),
            ALLEXCEPT ( 'Table', 'Table'[Week], 'Table'[Region], 'Table'[Location] ),
            'Table'[SLA Met] = "Yes"
        ),
    "Total",
        CALCULATE (
            COUNTROWS ( 'Table' ),
            ALLEXCEPT ( 'Table', 'Table'[Week], 'Table'[Region], 'Table'[Location] )
        )
)

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.