How to Automatically Track Regional Manager KPI Violations in Power BI

Hi Anonymous ,

To automate tracking Regional Manager (RM) KPI violations in Power BI, follow these steps. Combine Power Query transformations, DAX measures, and Power BI visuals to achieve the desired functionality.

1. Data Preparation in Power Query

Prepare and clean your data in Power Query to ensure the data model is ready for analysis.

Steps:

• Remove Duplicates: Ensure AUDIT_WGLL table has no duplicate entries based on AUDIT _DATE_ID, AUDITED_BY, and CUSTOMER_NAME.
• Add Derived Columns:

  • Month of Audit: Extract the month fromAUDIT _DATE_ID for aggregation.

    MonthName = Date.MonthName([AUDIT_DATE_ID])

  • Outlet Last Audit Date: Use a grouping and sorting method to calculate the last audit date for each

    CUSTOMER_NAME.

    LastAuditDate = Table.Group(AUDIT_WGLL, {"CUSTOMER_NAME"}, {{"MaxAuditDate", each List.Max([AUDIT_DATE_ID]), type date}})

  • Months Since Last Audit: Calculate the time gap in months between audits for the same CUSTOMER_NAME.

    MonthsSinceLastAudit = Duration.Days(Duration.From(DateTime.LocalNow() - [LastAuditDate])) / 30

    Filter out entries withMonthsSinceLastAudit < 3.

    2. Create Relationships

    Ensure relationships between your tables are properly defined in the model:

    • AUDIT_WGLL → Employee Table  (e.g., RM/ASM hierarchy).

AUDIT_WGLL  → Time Dimension  for time-based calculations.

3. Key Measures in DAX

Define DAX measures to evaluate each corporate principle.

Measure 1: RM Monthly Audits

This tracks the total audits performed by each RM.

RM_Audits = 
CALCULATE(
    COUNTROWS(AUDIT_WGLL),
    AUDIT_WGLL[AUDITED_BY_EMP_TYPE] = "Regional Manager"
)

Measure 2: FA Monthly Audits (Under RM)

This calculates total audits by all FAs under an RM.

FA_Audits_By_RM = 
CALCULATE(
    COUNTROWS(AUDIT_WGLL),
    AUDIT_WGLL[AUDITED_BY_EMP_TYPE] = "Field Auditor",
    AUDIT_WGLL[ASM_ID] = MAX(AUDIT_WGLL[ASM_ID])
)

Measure 3: Principle Violations

Identify violations for RMs based on corporate principles.

Principle_Violations = 
SWITCH(
    TRUE(),
    [RM_Audits] < 100 && [FA_Audits_By_RM] < 100, "RM & FA Violations",
    [RM_Audits] < 100, "RM Violation",
    [FA_Audits_By_RM] < 100, "FA Violation",
    "No Violation"
)

Measure 4: Recent Audit Check

Check if any outlet is audited again within 3 months.

RepeatedAuditViolation = 
CALCULATE(
    COUNTROWS(AUDIT_WGLL),
    DATEDIFF(
        LASTNONBLANK(AUDIT_WGLL[AUDIT_DATE_ID], 1),
        FIRSTNONBLANK(AUDIT_WGLL[AUDIT_DATE_ID], 1),
        MONTH
    ) < 3
)

4. Visualization

Use visuals in Power BI to highlight KPI violations.

Steps:

Matrix Table:

• Rows: Regional Manager Names (AUDITED_BY).
• Values:
  • RM_Audits, FA_Audits_by_RM.
  • Principle_Violations (conditional formatting for easy identification).

Conditional Formatting:

• Apply rules to highlight RMs with violations using the Principle_Violations measure.
• For example, color-code:
  • Red for "RM & FA Violations".
  • Orange for "RM Violation".
  • Yellow for "FA Violation".

KPI Cards:

• Display total RMs in violation and percentage compliance at a high level.

Bar Chart or Heat Map:

• Use to show trends in audit performance over time.

5. Dynamic Updates

Ensure the dashboard dynamically updates as new data arrives:

• Use Incremental Refresh for the AUDIT_WGLL table if you're working with a large dataset.
• Set up a scheduled data refresh in the Power BI service.

6. Notifications (Optional)

Set up alerts for KPI violations using Power BI's subscription feature or integrate with Power Automate to notify stakeholders when a violation occurs.

Please mark this as solution if it helps you. Appreciate Kudos.