CIRING CISSE — Customer Sales Intelligence
Actual expressions extracted from the delivered PBIX.

TABLE: CustomerSales
Total Sales =
SUM(CustomerSales[NetSales])

TABLE: CustomerSales
Gross Profit =
SUM(CustomerSales[GrossProfit])

TABLE: CustomerSales
Gross Margin % =
DIVIDE(
    [Gross Profit],
    [Total Sales],
    0
)

TABLE: CustomerSales
Orders =
DISTINCTCOUNT(CustomerSales[OrderID])

TABLE: CustomerSales
Active Customers =
DISTINCTCOUNT(CustomerSales[CustomerID])

TABLE: CustomerSales
Average Order Value =
DIVIDE(
    [Total Sales],
    [Orders],
    0
)

TABLE: CustomerSales
Sales LY =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR(DateTable[Date])
)

TABLE: CustomerSales
Sales YoY % =
DIVIDE(
    [Total Sales] - [Sales LY],
    [Sales LY],
    0
)

TABLE: CustomerSales
Customers LY =
CALCULATE(
    [Active Customers],
    SAMEPERIODLASTYEAR(DateTable[Date])
)

TABLE: CustomerSales
Orders LY =
CALCULATE(
    [Orders],
    SAMEPERIODLASTYEAR(DateTable[Date])
)

TABLE: CustomerSales
Orders YoY % =
DIVIDE(
    [Orders] - [Orders LY],
    [Orders LY],
    0
)

TABLE: CustomerSales
Customers YoY % =
DIVIDE(
    [Active Customers] - [Customers LY],
    [Customers LY],
    0
)

TABLE: CustomerSales
Average Order Value LY =
CALCULATE(
    [Average Order Value],
    SAMEPERIODLASTYEAR(DateTable[Date])
)

TABLE: CustomerSales
Average Order Value YoY % =
DIVIDE(
    [Average Order Value] - [Average Order Value LY],
    [Average Order Value LY],
    0
)

TABLE: CustomerSales
Gross Margin LY =
CALCULATE(
    [Gross Margin %],
    SAMEPERIODLASTYEAR(DateTable[Date])
)

TABLE: CustomerSales
Gross Margin vs LY =
[Gross Margin %] - [Gross Margin LY]

TABLE: CustomerSales
Gross Margin vs LY Display =
VAR ChangePP = [Gross Margin vs LY] * 100
RETURN
    FORMAT(
        ChangePP,
        "+0.00;-0.00;0.00"
    ) & " pp"

TABLE: CustomerMaster
Geographic Summary Display =
VAR SalesValue = [Total Sales]

VAR CountriesValue =
    DISTINCTCOUNT(CustomerMaster[Country])

VAR CustomersValue =
    [Active Customers]

VAR MarginValue =
    [Gross Margin %]

VAR SalesYoY =
    [Sales YoY %]

VAR MarginChange =
    [Gross Margin vs LY] * 100

VAR TopRegionTable =
    TOPN(
        1,
        ADDCOLUMNS(
            VALUES(CustomerMaster[Region]),
            "@Sales", [Total Sales]
        ),
        [@Sales], DESC
    )

VAR TopRegionName =
    CONCATENATEX(
        TopRegionTable,
        CustomerMaster[Region],
        ""
    )

VAR TopRegionSales =
    MAXX(
        TopRegionTable,
        [@Sales]
    )

RETURN
    "GEOGRAPHIC SUMMARY"
    & UNICHAR(10)
    & "────────────────"
    & UNICHAR(10) & UNICHAR(10)

    & "TOTAL SALES"
    & UNICHAR(10)
    & FORMAT(SalesValue / 1000000, "£0.00") & "M"
    & "   "
    & FORMAT(SalesYoY, "+0.0%;-0.0%;0.0%")

    & UNICHAR(10) & UNICHAR(10)

    & "COUNTRIES"
    & UNICHAR(10)
    & FORMAT(CountriesValue, "0")

    & UNICHAR(10) & UNICHAR(10)

    & "ACTIVE CUSTOMERS"
    & UNICHAR(10)
    & FORMAT(CustomersValue, "0")

    & UNICHAR(10) & UNICHAR(10)

    & "GROSS MARGIN"
    & UNICHAR(10)
    & FORMAT(MarginValue, "0.0%")
    & "   "
    & FORMAT(MarginChange, "+0.00;-0.00;0.00")
    & " pp"

    & UNICHAR(10) & UNICHAR(10)

    & "REGIONAL LEADER"
    & UNICHAR(10)
    & TopRegionName
    & UNICHAR(10)
    & FORMAT(TopRegionSales / 1000000, "£0.00") & "M"

TABLE: CustomerMaster
Countries =
DISTINCTCOUNT(CustomerMaster[Country])

TABLE: CustomerMaster
Selected Customer =
SELECTEDVALUE(
    CustomerMaster[CustomerName],
    "Select a Customer"
)

TABLE: CustomerMaster
Customer Profile Subtitle =
VAR Segment =
    SELECTEDVALUE(CustomerMaster[CustomerSegment])
VAR City =
    SELECTEDVALUE(CustomerMaster[City])
VAR Country =
    SELECTEDVALUE(CustomerMaster[Country])
VAR Industry =
    SELECTEDVALUE(CustomerMaster[Industry])

RETURN
    Segment
        & "  •  "
        & City
        & ", "
        & Country
        & "  •  "
        & Industry

TABLE: SalesTargets
Sales Target =
SUM(SalesTargets[SalesTarget])

TABLE: SalesTargets
Target Variance =
[Total Sales] - [Sales Target]

TABLE: SalesTargets
Target Variance % =
DIVIDE(
    [Target Variance],
    [Sales Target],
    0
)

TABLE: SalesTargets
Target Attainment % =
DIVIDE(
    [Total Sales],
    [Sales Target],
    0
)

TABLE: DimSalesperson
Salesperson Rank =
RANKX(
    ALLSELECTED(DimSalesperson[Salesperson]),
    [Total Sales],
    ,
    DESC,
    DENSE
)

CALCULATED TABLE: DateTable =
ADDCOLUMNS(
    CALENDAR(
        MIN(CustomerSales[OrderDate]),
        MAX(CustomerSales[OrderDate])
    ),
    "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: DimSalesperson =
DISTINCT(
    UNION(
        SELECTCOLUMNS(
            CustomerSales,
            "Salesperson", CustomerSales[Salesperson]
        ),
        SELECTCOLUMNS(
            SalesTargets,
            "Salesperson", SalesTargets[Salesperson]
        )
    )
)

CALCULATED TABLE: Parameter ={
    ("Total Sales", NAMEOF('CustomerSales'[Total Sales]), 0),
    ("Gross Profit", NAMEOF('CustomerSales'[Gross Profit]), 1),
    ("Orders", NAMEOF('CustomerSales'[Orders]), 2),
    ("Gross Margin %", NAMEOF('CustomerSales'[Gross Margin %]), 3)
}