CIRING CISSE — Supplier Performance
Actual expressions extracted from the delivered PBIX.

TABLE: SupplierPerformance
Purchase Value =
SUM(SupplierPerformance[POValue])

TABLE: SupplierPerformance
PO Lines =
DISTINCTCOUNT(SupplierPerformance[POLineID])

TABLE: SupplierPerformance
On-Time Deliveries =
CALCULATE(
    COUNTROWS(SupplierPerformance),
    SupplierPerformance[DeliveryStatus] = "On Time"
)

TABLE: SupplierPerformance
On-Time Delivery % =
DIVIDE(
    [On-Time Deliveries],
    COUNTROWS(SupplierPerformance),
    0
)

TABLE: SupplierPerformance
Average Lead Time =
AVERAGE(SupplierPerformance[LeadTimeDays])

TABLE: SupplierPerformance
Rejected Qty =
SUM(SupplierPerformance[RejectedQty])

TABLE: SupplierPerformance
Received Qty =
SUM(SupplierPerformance[ReceivedQty])

TABLE: SupplierPerformance
Reject Rate % =
DIVIDE(
    [Rejected Qty],
    [Received Qty],
    0
)

TABLE: SupplierPerformance
Open Qty =
SUM(SupplierPerformance[OpenQty])

TABLE: SupplierPerformance
Delivery Score =
[On-Time Delivery %] * 100

TABLE: SupplierPerformance
Quality Score =
VAR RejectRate = [Reject Rate %]
RETURN
    MAX(
        0,
        100 - (RejectRate * 1000)
    )

TABLE: SupplierPerformance
Lead Time Score =
VAR Days = [Average Lead Time]
RETURN
    SWITCH(
        TRUE(),
        Days <= 15, 100,
        Days <= 20, 90,
        Days <= 30, 75,
        Days <= 40, 60,
        Days <= 50, 40,
        20
    )

TABLE: SupplierPerformance
Supplier Performance Score =
VAR Delivery = [Delivery Score]
VAR Quality = [Quality Score]
VAR LeadTime = [Lead Time Score]

RETURN
    (Delivery * 0.40)
    + (Quality * 0.35)
    + (LeadTime * 0.25)

TABLE: SupplierPerformance
Supplier Performance Rating =
VAR Score = [Supplier Performance Score]

RETURN
    SWITCH(
        TRUE(),
        Score >= 90, "Excellent",
        Score >= 80, "Good",
        Score >= 70, "Watch",
        Score >= 60, "At Risk",
        "Critical"
    )

TABLE: SupplierPerformance
Average Supplier Score =
AVERAGEX(
    VALUES(SupplierPerformance[SupplierName]),
    [Supplier Performance Score]
)

TABLE: SupplierPerformance
Supplier Count =
DISTINCTCOUNT(SupplierPerformance[SupplierID])

TABLE: SupplierPerformance
Excellent Suppliers =
COUNTROWS(
    FILTER(
        VALUES(SupplierPerformance[SupplierName]),
        [Supplier Performance Score] >= 90
    )
)

TABLE: SupplierPerformance
Good Suppliers =
COUNTROWS(
    FILTER(
        VALUES(SupplierPerformance[SupplierName]),
        [Supplier Performance Score] >= 80
            && [Supplier Performance Score] < 90
    )
)

TABLE: SupplierPerformance
Watch Suppliers =
COUNTROWS(
    FILTER(
        VALUES(SupplierPerformance[SupplierName]),
        [Supplier Performance Score] >= 70
            && [Supplier Performance Score] < 80
    )
)

TABLE: SupplierPerformance
At Risk Suppliers =
COUNTROWS(
    FILTER(
        VALUES(SupplierPerformance[SupplierName]),
        [Supplier Performance Score] >= 60
            && [Supplier Performance Score] < 70
    )
)

TABLE: SupplierPerformance
Critical Suppliers =
COUNTROWS(
    FILTER(
        VALUES(SupplierPerformance[SupplierName]),
        [Supplier Performance Score] < 60
    )
)

TABLE: SupplierPerformance
Selected Supplier =
SELECTEDVALUE(
    SupplierPerformance[SupplierName],
    "Select a Supplier"
)

TABLE: SupplierPerformance
Supplier Profile Subtitle =
VAR Category =
    SELECTEDVALUE(SupplierPerformance[SupplierCategory])

VAR Country =
    SELECTEDVALUE(SupplierPerformance[Country])

VAR Buyer =
    SELECTEDVALUE(SupplierPerformance[Buyer])

RETURN
    Category
    & "  •  "
    & Country
    & "  •  Buyer: "
    & Buyer

TABLE: SupplierPerformance
Supplier Risk Assessment =
VAR Score =
    [Supplier Performance Score]

VAR OTD =
    [On-Time Delivery %]

VAR RejectRate =
    [Reject Rate %]

VAR LeadTime =
    [Average Lead Time]

VAR Spend =
    [Purchase Value]

VAR RiskLevel =
    SWITCH(
        TRUE(),
        Score < 60, "CRITICAL",
        Score < 70, "AT RISK",
        Score < 80, "WATCH",
        Score < 90, "GOOD",
        "EXCELLENT"
    )

VAR DeliveryComment =
    SWITCH(
        TRUE(),
        OTD < 0.80, "Delivery reliability is significantly below expectation.",
        OTD < 0.90, "Delivery performance requires attention.",
        OTD < 0.95, "Delivery performance is acceptable but could improve.",
        "Delivery performance is strong."
    )

VAR QualityComment =
    SWITCH(
        TRUE(),
        RejectRate >= 0.05, "Quality rejection levels are high.",
        RejectRate >= 0.03, "Quality performance requires monitoring.",
        RejectRate >= 0.01, "Quality performance is generally stable.",
        "Quality performance is strong with minimal rejects."
    )

VAR LeadTimeComment =
    SWITCH(
        TRUE(),
        LeadTime > 40, "Average lead time is relatively long.",
        LeadTime > 30, "Lead time should be monitored.",
        LeadTime > 20, "Lead time performance is acceptable.",
        "Lead time performance is strong."
    )

VAR SpendText =
    SWITCH(
        TRUE(),
        Spend >= 1000000, FORMAT(Spend / 1000000, "£0.00") & "M",
        Spend >= 1000, FORMAT(Spend / 1000, "£0") & "K",
        FORMAT(Spend, "£0")
    )

RETURN
    RiskLevel
        & UNICHAR(10)
        & UNICHAR(10)
        & DeliveryComment
        & " "
        & QualityComment
        & " "
        & LeadTimeComment
        & UNICHAR(10)
        & UNICHAR(10)
        & SpendText
        & " of purchasing spend is associated with this supplier."

CALCULATED TABLE: DateTable =
ADDCOLUMNS(
    CALENDAR(
        MIN(SupplierPerformance[PODate]),
        MAX(SupplierPerformance[PODate])
    ),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "Month Number", MONTH([Date]),
    "Year Month", FORMAT([Date], "YYYY-MM"),

    "Fiscal Year",
        VAR FYStart =
            IF(
                MONTH([Date]) >= 3,
                YEAR([Date]),
                YEAR([Date]) - 1
            )
        RETURN
            FORMAT(FYStart, "00")
            & "/"
            & RIGHT(FORMAT(FYStart + 1, "0000"), 2),

    "Fiscal Month Number",
        MOD(MONTH([Date]) - 3, 12) + 1
)

CALCULATED TABLE: Parameter ={
    ("Purchase Value", NAMEOF('SupplierPerformance'[Purchase Value]), 0),
    ("On-Time Delivery %", NAMEOF('SupplierPerformance'[On-Time Delivery %]), 1),

    ("Reject Rate %", NAMEOF('SupplierPerformance'[Reject Rate %]), 3)
}

CALCULATED TABLE: Parameter 2 ={
    ("Delivery Score", NAMEOF('SupplierPerformance'[Delivery Score]), 0),
    ("Quality Score", NAMEOF('SupplierPerformance'[Quality Score]), 1),
    ("Lead Time Score", NAMEOF('SupplierPerformance'[Lead Time Score]), 2)
}