How to Automatically Track Regional Manager KPI Violations in Power BI

[afiqhaziq589]

Hi Fabric Community,

I’m working on a Power BI dashboard to track the KPI of Regional Managers (RM) in real-time. The challenge is to dynamically identify RMs who violate company audit principles, Use any measures or DAX to ensuring the process updates automatically when new data arrives.

🔍Situation:

• Sales hierarchy: National Manager (NM) → State Manager (SM) → Regional Manager (RM) → Field Auditor (FA)
• Field Auditors (FA) conduct compliance audits in retail outlets, and Regional Managers (RM) verify their work.
• Corporate Principles for RMs:
  1. Each RM must conduct at least 100 audits per month.
  2. The FAs under an RM must also conduct at least 100 audits per month.
  3. FAs cannot audit the same outlet within 3 months.

📊Data Structure:

Table: AUDIT_WGLL (Audit Log Table)

Column :

• AUDIT_DATE_ID – Date of the audit (Format: Date)
• AUDITED_BY – The individual who performed the audit (e.g., Area Sales Manager - ASM)
• AUDITED_BY_EMP_TYPE – Designation of the person conducting the audit (e.g., ASM or ASE)
• ASE_ID – Unique identifier for the Area Sales Executive (ASE)
• ASE_NAME – Name of the Area Sales Executive (ASE)
• ASM_ID – Unique identifier for the Area Sales Manager (ASM)
• ASM_NAME – Name of the Area Sales Manager (ASM)
• CUSTOMER_NAME – Name of the audited retail outlet
• DIST_ID – Distribution zone identifier

 

🎯Goal:

I need to create a fully automated process that:
✅Identifies which RM did not meet the corporate principles.
✅Highlights which specific principle(s) were violated.
✅Provides an overview of all RM performance without manual filtering.
✅Updates dynamically when new data is added.

🚀Challenge:

How can I achieve this tracking?
I want to rely on Power Query transformations, filtering, DAX as long as i achieve my objective

Would love to hear suggestions or best practices from the community!

Thanks in advance! 😊

[FarhanJeelani · Accepted solution]

Hi @afiqhaziq589 ,

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.