Power BI Masterclass

// Stap 1: Data laden van Excel
let
    Bron = Excel.Workbook(File.Contents("C:\Data\Sales.xlsx"), null, true),
    Sales_Sheet = Bron{[Item="Sales",Kind="Sheet"]}[Data],
    
    // Stap 2: Headers promoveren
    Headers = Table.PromoteHeaders(Sales_Sheet, [PromoteAllScalars=true]),
    
    // Stap 3: Data types instellen
    ChangedType = Table.TransformColumnTypes(Headers,{
        {"OrderID", Int64.Type},
        {"OrderDate", type date},
        {"CustomerID", Int64.Type},
        {"ProductID", Int64.Type},
        {"Quantity", Int64.Type},
        {"UnitPrice", Currency.Type},
        {"TotalAmount", Currency.Type},
        {"Region", type text},
        {"SalesPerson", type text}
    }),
    
    // Stap 4: Berekende kolom toevoegen
    AddYearMonth = Table.AddColumn(ChangedType, "YearMonth", 
        each Date.ToText([OrderDate], "YYYY-MM"), type text),
    
    // Stap 5: Filters toepassen
    FilteredRows = Table.SelectRows(AddYearMonth, each [TotalAmount] > 0),
    
    // Stap 6: Ongeldige data verwijderen
    RemoveNulls = Table.SelectRows(FilteredRows, each 
        not List.Contains({null, ""}, [CustomerID])),
    
    // Stap 7: Sorteren
    SortedRows = Table.Sort(RemoveNulls,{{"OrderDate", Order.Descending}})
in
    SortedRows

// Stap 8: Parameters voor hergebruik
RegionalSettings = [
    CurrencySymbol = "€",
    DecimalSeparator = ",",
    ThousandSeparator = "."
],

// Stap 9: Function voor herhaalbare transformaties
CleanCurrencyColumn = (table as table, columnName as text) as table =>
    let
        ReplaceSymbols = Table.ReplaceValue(table,
            each Record.Field(_, columnName),
            each Text.Replace(Text.Replace(_, "€", ""), ".", ""),
            Replacer.ReplaceText,
            {columnName}
        ),
        ToNumber = Table.TransformColumnTypes(ReplaceSymbols,
            {{columnName, Currency.Type}}
        )
    in
        ToNumber
        

// ========================
// BASICS MEASURES
// ========================

// Totale verkopen (basis measure)
Total Sales = SUM(Sales[TotalAmount])

// Aantal orders (COUNTROWS)
Total Orders = COUNTROWS(Sales)

// Aantal unieke klanten (DISTINCTCOUNT)
Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

// Gemiddelde order waarde (AVERAGE)
Average Order Value = AVERAGE(Sales[TotalAmount])

// ========================
// TIME INTELLIGENCE
// ========================

// Verkopen dit jaar (TOTALYTD)
Sales YTD = TOTALYTD(
    [Total Sales],
    'Date'[Date],
    "31/12"  // Jaareinde
)

// Verkopen vorig jaar (SAMEPERIODLASTYEAR)
Sales Previous Year = 
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR('Date'[Date])
)

// YoY groei percentage
Sales YoY Growth = 
DIVIDE(
    [Total Sales] - [Sales Previous Year],
    [Sales Previous Year],
    0
)

// Rolling 12 maanden (TRAILING 12 MONTHS)
Sales Rolling 12M = 
CALCULATE(
    [Total Sales],
    DATESINPERIOD(
        'Date'[Date],
        MAX('Date'[Date]),
        -12,
        MONTH
    )
)

// ========================
// CONDITIONAL MEASURES
// ========================

// Verkopen alleen voor premium klanten
Premium Sales = 
CALCULATE(
    [Total Sales],
    Customer[Segment] = "Premium"
)

// Verkopen boven €1000 (high-value orders)
High Value Sales = 
CALCULATE(
    [Total Sales],
    Sales[TotalAmount] > 1000
)

// Percentage van totale verkopen
Premium Sales % = 
DIVIDE(
    [Premium Sales],
    [Total Sales],
    0
)

// ========================
// RANKING & TOP N
// ========================

// Rangschikking van producten op verkopen
Product Sales Rank = 
RANKX(
    ALL(Product[ProductName]),
    [Total Sales],
    ,
    DESC,
    Dense
)

// Top 5 producten identificeren
Is Top 5 Product = 
VAR CurrentProductSales = [Total Sales]
VAR Top5Products = 
    TOPN(
        5,
        VALUES(Product[ProductName]),
        [Total Sales]
    )
RETURN
    IF(
        SELECTEDVALUE(Product[ProductName]) IN Top5Products,
        "Top 5",
        "Other"
    )

// ========================
// DYNAMIC FORMATTING
// ========================

// Voorwaardelijke opmaak op basis van target
Sales vs Target = 
VAR SalesAmount = [Total Sales]
VAR TargetAmount = [Sales Target]
RETURN
    SalesAmount - TargetAmount

// Format als € of %
Sales vs Target Formatted = 
VAR Difference = [Sales vs Target]
VAR TargetAmount = [Sales Target]
VAR Percentage = DIVIDE(Difference, TargetAmount, 0)
RETURN
    IF(
        ABS(Percentage) < 0.01,  // Kleine verschillen
        FORMAT(Difference, "€#,##0"),
        FORMAT(Percentage, "0.0%")
    )

// ========================
// AI-POWERED MEASURES
// ========================

// Voorbeeld met AI visuals (Quick Insights)
// Power BI kan automatisch patronen detecteren
// Gebruik: Home tab → Quick Insights

// ========================
// PERFORMANCE OPTIMALISATIE
// ========================

// Gebruik VARIABLES voor betere performance
Optimized Sales Calculation = 
VAR TotalRevenue = SUM(Sales[TotalAmount])
VAR TotalCost = SUM(Sales[CostAmount])
VAR TotalQuantity = SUM(Sales[Quantity])
RETURN
    DIVIDE(TotalRevenue - TotalCost, TotalQuantity, 0)

// ========================
// DUTCH SPECIFIC FORMATTING
// ========================

// Nederlands geld formaat met euro teken
Sales Formatted NL = 
FORMAT(
    [Total Sales],
    "€ #,##0.00;€ -#,##0.00;€ 0.00"
)

// Nederlandse datum formaten
Last Order Date NL = 
FORMAT(
    MAX(Sales[OrderDate]),
    "dd-MM-yyyy"
)

// Nederlandse duizend/miljoen afkortingen
Sales Abbreviated NL = 
VAR Value = [Total Sales]
RETURN
    SWITCH(
        TRUE(),
        Value >= 1000000, FORMAT(Value / 1000000, "0.0") & "M",
        Value >= 1000, FORMAT(Value / 1000, "0.0") & "K",
        FORMAT(Value, "0")
    ) & " €"

// ========================
// REAL-WORLD BUSINESS SCENARIO
// ========================

// Nederlandse BTW berekening (21%)
Sales Excluding VAT = 
DIVIDE([Total Sales], 1.21)

// Kwartaal performance vs vorig jaar
Sales Growth QoQ = 
VAR CurrentQuarter = [Total Sales]
VAR PreviousQuarter = 
    CALCULATE(
        [Total Sales],
        DATEADD('Date'[Date], -1, QUARTER)
    )
RETURN
    DIVIDE(
        CurrentQuarter - PreviousQuarter,
        PreviousQuarter,
        0
    )

// Customer segmentation gebaseerd op RFM
Customer Segment = 
VAR Recency = DATEDIFF(MAX(Sales[OrderDate]), TODAY(), DAY)
VAR Frequency = [Total Orders]
VAR Monetary = [Total Sales]
RETURN
    SWITCH(
        TRUE(),
        Recency <= 30 && Frequency >= 10 && Monetary >= 5000, "Champions",
        Recency <= 60 && Frequency >= 5, "Loyal Customers",
        Monetary >= 10000, "High Value",
        "Need Attention"
    )