Chinese Idioms Report

I originally presented this at the Sydney Power BI User Group in June 2019. It is intended as an educational tool to explore and learn Chinese idioms.

Key features:

• Imports a library of Chinese idioms from https://cantonese.sheik.co.uk/dictionary/idioms/
• Displays a new idiom each day on the first page.
• Includes text-to-speech feature: the Idiom table contains URLs for the Ekho text-to-speech service (Cantonese and Mandarin), which are visualized using the HTML Content visual.
• Allows filtering by Chinese character, Chinese idiom text or English translation.
• Data model uses many-to-many relationship between Character and Idiom tables, via bridge table

Revenue Variance Analysis

I originally presented this at the Data, Insight and Power BI User Group, Auckland, New Zealand, in June 2017. It demonstrates Price-Volume-Mix variance analysis calculations for a typical sales dataset.

Key features:

• Using Contoso sample data, demonstrates Price-Volume-Mix variance analysis calculations, using a pattern that can be adapted to any sales dataset.
• User can select any pair of Scenarios for pairwise comparison. ‘Current Scenario’ has active relationship with fact table, and ‘Previous Scenario’ has inactive relationship with ‘Current Scenario’.

• The inactive relationship activated by specific “Previous” measures, for example Sales Quantity Previous:

Sales Quantity Previous = 
CALCULATE ( 
    [Sales Quantity],
    ALL ( 'Current Scenario' ),
    USERELATIONSHIP( 'Current Scenario'[Current Scenario], 'Previous Scenario'[Previous Scenario] )
)

• Measures are written to handle scenarios such as missing prices in either Current or Previous Scenarios.
• Variance measures are written so that calculations are performed at a sufficiently low level and aggregated, for example Revenue Variance due to Price:

Revenue Variance due to Price = 
-- Note: Nested SUMX may perform better than SUMMARIZE
-- if almost all combinations occur in fact table
SUMX (
    CALCULATETABLE (
        SUMMARIZE (
            Sales,
            Store[StoreKey],
            'Product'[Product Category Name],
            'Date'[End of Month]
        ),
        UNION (
            VALUES ( 'Current Scenario'[Current Scenario] ),
            TREATAS (
                VALUES ( 'Previous Scenario'[Previous Scenario] ),
                'Current Scenario'[Current Scenario]
            )
        )
    ),
    [Price Variance For Analysis] * [Sales Quantity]
)

Personal Finance Projection

I originally presented this as part of “Essential Financial Calculations in Power BI” at SQL Saturday 866 in Auckland, New Zealand, October 2019. This is an example of a small financial calculator.

Key features:

• This report relies on user input of key parameters, via slicers.
• Income, Spending, Investment Returns and Assets are projected using provided growth rates.
• A measure is used to determine the age at which assets are depleted.
• The Assets measure is shown below as an example:

Assets = 
VAR MaxFilteredAge = MAX ( Age[Age] )
VAR CurrentAge = [Current Age Value]
VAR InvestmentReturn = [Investment Return Value]
VAR AssetsClosing =
    IF (
        MaxFilteredAge >= CurrentAge,
        // Future value of current assets
        [Current Assets Value] * ( 1 + InvestmentReturn ) ^ ( MaxFilteredAge - CurrentAge + 1 )
        // Future value of Saving each year = Income - Spending
        + CALCULATE ( 
            SUMX (
                Age,
                [Saving] * ( 1 + InvestmentReturn ) ^ ( MaxFilteredAge - Age[Age] )
            ),
            Age[Age] <= MaxFilteredAge && Age[Age] >= CurrentAge
        )
    )
RETURN
    AssetsClosing

NPV and IRR calculation for multiple projects

I originally presented this as part of “Essential Financial Calculations in Power BI” at SQL Saturday 866 in Auckland, New Zealand, October 2019.

Key features:

• This report was designed to take a simple dataset containing cashflow by project/date, and show how the XNPV and XIRR functions make it easy to calculate NPV and IRR for single or groups of projects.
• Payback periods (discounted and non-discounted) are also calculated using FIRSTNONBLANK to detect the first date on which net cashflow is non-negative.
• The discount rate is controlled via slicer.
• Some sample measures shown below:

Project NPV (project start date) = 
XNPV ( Cashflow, Cashflow[Cashflow], Cashflow[Date], [Discount Rate Value] )

Discounted Payback Period (months) = 
VAR ProjectStart = MIN ( Cashflow[Date] )
VAR FirstPositiveDate =
FIRSTNONBLANK (
    SUMMARIZE ( Cashflow, 'Date'[Date] ),
    VAR Cumulative =
        [Project NPV Cumulative]
    RETURN 
        IF ( Cumulative >= 0, 1 )
)
RETURN
    DATEDIFF ( ProjectStart, FirstPositiveDate, MONTH )

Portfolio Risk-Return

I originally presented this as part of “Essential Financial Calculations in Power BI” at SQL Saturday 866 in Auckland, New Zealand, October 2019.

Key features:

• Demonstrates basic mean/variance calculations for portfolios of assets, given expected return, covariance and weights in each portfolio.
• Data model is configured as follows, with Asset as a dimensions, and Return, Covariance, and Scenarios (portfolios) as fact tables:

• Portfolio metrics are calculated by iteration over the Scenarios table, for example:

Portfolio Return = 
VAR SelectedScenario = SELECTEDVALUE ( 'Scenarios'[Scenario Name] )
RETURN
IF ( 
    NOT ISBLANK ( SelectedScenario ),
    -- If sum of Weights <> 1, normalise by dividing weights by TotalWeight
    VAR TotalWeight = SUM ( 'Scenarios'[Weight] )
    RETURN
        DIVIDE (
            -- For each row of Scenarios, multiply weight by return of corresponding asset
            SUMX ( 
                'Scenarios',
                'Scenarios'[Weight] * [Single Asset Return]
            ),
            TotalWeight
        )
)