Mastering DAX

Exercise 13.10.dax

--https://dax.do/HjDPEEQlk8ADkj
--13.10 - Sales by year
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Calendar Year],
    "Sales", [Sales Amount],
    "Pct",
        DIVIDE (
            [Sales Amount],
            CALCULATE ( [Sales Amount], ALL ( 'Date'[Calendar Year] ) )
        ),
    "Position",
        IF (
            NOT ISBLANK ( [Sales Amount] ),
            RANKX ( ALLSELECTED ( 'Date'[Calendar Year] ), [Sales Amount] )
        )
)

Exercise 13.20.dax

--https://dax.do/7A7NUrculwKMu1
--13.20 - Filtering and grouping sales - Basic
EVALUATE
VAR ProductTable =
    TREATAS ( { "Red", "Blue" }, 'Product'[Color] )
VAR BrandTable =
    TREATAS ( { "Fabrikam", "Contoso" }, 'Product'[Brand] )
VAR Output =
    SUMMARIZECOLUMNS (
        'Product'[Color],
        Customer[Education],
        ProductTable,
        BrandTable,
        "Sales", [Sales Amount]
    )
RETURN
    Output
    
--13.20 - Filtering and grouping sales - Advanced
EVALUATE
VAR ProductTable =
    TREATAS ( { "Red", "Blue" }, 'Product'[Color] )
VAR BrandTable =
    TREATAS ( { "Fabrikam", "Contoso" }, 'Product'[Brand] )
VAR FilteredTable =
    SUMMARIZECOLUMNS (
        'Product'[Color],
        Customer[Education],
        ProductTable,
        BrandTable,
        "Sales", [Sales Amount]
    )
VAR SalesAvg =
    AVERAGEX ( FilteredTable, [Sales] )
VAR Output =
    FILTER ( FilteredTable, [Sales Amount] > SalesAvg )
RETURN
    Output

Exercise 13.30.dax

--https://dax.do/hd8am1kc5xgSMw
--13.30 - Using TOPN and GENERATE
EVALUATE
GENERATE (
    /*
    Recall values will return all years regardless if there was a sale, and summarize is used to remove nulls
    */
    --VALUES ( 'Date'[Calendar Year] ),
    SUMMARIZE (
        'Sales',
        'Date'[Calendar Year]
    ),
    VAR SalesTopN =
        TOPN (
            VAR n = 3 RETURN n,
            (
                ADDCOLUMNS (
                    VALUES ( 'Product'[Product Name] ),
                    "SalesAmt", [Sales Amount],
                    "Product Not in Top N", 0
                )
            ),
            [SalesAmt]
        )
    VAR SalesTop =
        SUMX ( SalesTopN, [SalesAmt] )
    VAR SalesAll = [Sales Amount]
    VAR SalesRemaining = SalesAll - SalesTop
    VAR ProductOther = { ( "Other Products", SalesRemaining, 1 ) }
    VAR Result =
        UNION ( SalesTopN, ProductOther )
    RETURN
        Result
)
ORDER BY
    'Date'[Calendar Year],
    [Product Not in Top N]

Exercise 13.40.dax

--https://dax.do/rJo1hRVcTvrTBB/
--13.40 - Sales to top customers - Basic
EVALUATE
VAR CustomersTop =
    TOPN ( VAR n = 10 RETURN n, VALUES ( Sales[CustomerKey] ), [Sales Amount] )
VAR Output =
    SUMMARIZECOLUMNS (
        'Date'[Calendar Year],
        CustomersTop,
        "Sales", [Sales Amount]
    )
RETURN
    Output
ORDER BY 'Date'[Calendar Year]

--13.40 - Sales to top customers - Advanced
EVALUATE
VAR CustomersTop =
    TOPN ( VAR n = 10 RETURN n, VALUES ( Sales[CustomerKey] ), [Sales Amount] )
RETURN
    GENERATE (
        SUMMARIZE ( 'Sales', 'Date'[Calendar Year] ),
        ADDCOLUMNS (
            VAR SalesTopN =
                CALCULATE ( [Sales Amount], CustomersTop )
            VAR LabelTopN =
                ROW ( "Name", "Sales of Top 10 Product", "Sales", SalesTopN )
            VAR LabelOthers =
                ROW (
                    "Name", "Sales of Product Not in Top 10",
                    "Sales", [Sales Amount] - SalesTopN
                )
            VAR SalesTable =
                UNION ( LabelTopN, LabelOthers )
            VAR Output = SalesTable
            RETURN
                Output,
            "Percentage of Sales", DIVIDE ( [Sales], [Sales Amount] )
        )
    )
ORDER BY
    'Date'[Calendar Year],
    [Name] DESC

Exercise 13.50.dax

--https://dax.do/luCncYJDk1I9Ne/
--13.50 - Sales of top three colors
DEFINE
    MEASURE Sales[Top3] =
        SUMX (
            TOPN (
                3,
                ADDCOLUMNS ( VALUES ( 'Product'[Color] ), "Sales", [Sales Amount] ),
                [Sales]
            ),
            [Sales]
        )
EVALUATE
SUMMARIZECOLUMNS ( 'Product'[Brand], "SalesTop3", [Top3] )