In this article, I am going to log my learnings about building an adventure works dashboard in Power BI desktop.

Build a dashboard from scratch in Power BI using features like bookmarks, drill through filters, parameters, tooltips, and more.

Power BI Desktop Benchmark Assessment

[Link]

1. Get Data into SQL Server

i. AdventureWorks Sales Data 2020

table schema

CREATE TABLE [dbo].[AdventureWorks Sales Data 2020] (
[OrderDate] date,
[StockDate] date,
[OrderNumber] varchar(50),
[ProductKey] int,
[CustomerKey] int,
[TerritoryKey] int,
[OrderLineItem] int,
[OrderQuantity] int
)

Importing data from a CSV flat file into SQL Server using import wizard.

Data in SQL server:

Now,

ii. AdventureWorks Sales Data 2021:

Import using T-SQL Script

create a table

BEGIN TRY

    BEGIN TRANSACTION T3SDT2021; -- Start the transaction

    -- Your main content of the script here

        IF OBJECT_ID(N'[CustomAdventureWorksDW].[dbo].[AdventureWorks Sales Data 2021]', N'U') IS NOT NULL
            BEGIN
                PRINT('[CustomAdventureWorksDW].[dbo].[AdventureWorks Sales Data 2021] EXISTS')
                DROP TABLE [CustomAdventureWorksDW].[dbo].[AdventureWorks Sales Data 2021]
            END

        ELSE
            BEGIN
                PRINT('[CustomAdventureWorksDW].[dbo].[AdventureWorks Sales Data 2021] DOES NOT EXIST')
                CREATE TABLE [CustomAdventureWorksDW].[dbo].[AdventureWorks Sales Data 2021] (
                    [OrderDate] date,
                    [StockDate] date,
                    [OrderNumber] varchar(50) NOT NULL,
                    [ProductKey] int NOT NULL,
                    [CustomerKey] int NOT NULL,
                    [TerritoryKey] int NOT NULL,
                    [OrderLineItem] int NOT NULL,
                    [OrderQuantity] int NOT NULL
                    )
            END

    -- If no exception occurs, commit the transaction
    COMMIT TRANSACTION T3SDT2021;

END TRY

BEGIN CATCH
    -- If an exception occurs, roll back the transaction
    IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION T3SDT2021;
            -- Optionally, you can log or handle the error here
            -- For example:
            PRINT 'Error occurred: ' + ERROR_MESSAGE();
        END
END CATCH;

Bulk insert:

BEGIN TRY

    BEGIN TRANSACTION T4SDBI2021; -- Start the transaction

    -- Your main content of the script here
        -- import the file
        BULK INSERT [CustomAdventureWorksDW].[dbo].[AdventureWorks Sales Data 2021]
        FROM 'Path\AdventureWorks Sales Data 2021.csv'
        WITH (
            FORMAT='CSV',
            FIRSTROW=2,
            FIELDTERMINATOR = ',', 
            ROWTERMINATOR = '0x0a'
            )

    -- If no exception occurs, commit the transaction
    COMMIT TRANSACTION T4SDBI2021;

END TRY

BEGIN CATCH
    -- If an exception occurs, roll back the transaction
    IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION T4SDBI2021;
            -- Optionally, you can log or handle the error here
            -- For example:
            PRINT 'Error occurred: ' + ERROR_MESSAGE();
        END
END CATCH;

iii AdventureWorks Sales Data 2022

create table

BEGIN TRY

    BEGIN TRANSACTION T5SDT2022; -- Start the transaction

    -- Your main content of the script here

        IF OBJECT_ID(N'[CustomAdventureWorksDW].[dbo].[AdventureWorks Sales Data 2022]', N'U') IS NOT NULL
            BEGIN
                PRINT('[CustomAdventureWorksDW].[dbo].[AdventureWorks Sales Data 2022] EXISTS')
                DROP TABLE [CustomAdventureWorksDW].[dbo].[AdventureWorks Sales Data 2022]
            END

        ELSE
            BEGIN
                PRINT('[CustomAdventureWorksDW].[dbo].[AdventureWorks Sales Data 2022] DOES NOT EXIST')
                CREATE TABLE [CustomAdventureWorksDW].[dbo].[AdventureWorks Sales Data 2022] (
                    [OrderDate] date,
                    [StockDate] date,
                    [OrderNumber] varchar(50) NOT NULL,
                    [ProductKey] int NOT NULL,
                    [CustomerKey] int NOT NULL,
                    [TerritoryKey] int NOT NULL,
                    [OrderLineItem] int NOT NULL,
                    [OrderQuantity] int NOT NULL
                    )
            END

    -- If no exception occurs, commit the transaction
    COMMIT TRANSACTION T5SDT2022;

END TRY

BEGIN CATCH
    -- If an exception occurs, roll back the transaction
    IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION T5SDT2022;
            -- Optionally, you can log or handle the error here
            -- For example:
            PRINT 'Error occurred: ' + ERROR_MESSAGE();
        END
END CATCH;

bulk import

BEGIN TRY

    BEGIN TRANSACTION T6SDBI2022; -- Start the transaction

    -- Your main content of the script here
        -- import the file
        BULK INSERT [CustomAdventureWorksDW].[dbo].[AdventureWorks Sales Data 2022]
        FROM 'Path\AdventureWorks Sales Data 2022.csv'
        WITH (
            FORMAT='CSV',
            FIRSTROW=2,
            FIELDTERMINATOR = ',', 
            ROWTERMINATOR = '0x0a'
            )

    -- If no exception occurs, commit the transaction
    COMMIT TRANSACTION T6SDBI2022;

END TRY

BEGIN CATCH
    -- If an exception occurs, roll back the transaction
    IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION T6SDBI2022;
            -- Optionally, you can log or handle the error here
            -- For example:
            PRINT 'Error occurred: ' + ERROR_MESSAGE();
        END
END CATCH;

iv. Combining into sales data:

table:

BEGIN TRY
    BEGIN TRANSACTION T7SDDT;
        IF OBJECT_ID(N'[CustomAdventureWorksDW].[dbo].[AdventureWorks Sales Data]') IS NOT NULL
            BEGIN 
                DROP TABLE [CustomAdventureWorksDW].[dbo].[AdventureWorks Sales Data]
                PRINT('SUCCESSFULLY DROPPED')
            END
    COMMIT TRANSACTION T7SDDT;
END TRY

BEGIN CATCH
    -- If an exception occurs, roll back the transaction
    IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION T7SDDT;
            -- Optionally, you can log or handle the error here
            -- For example:
            PRINT 'Error occurred: ' + ERROR_MESSAGE();
        END
END CATCH;

select * into

BEGIN TRY
    BEGIN TRANSACTION T8SDCD;

        SELECT *
        INTO [CustomAdventureWorksDW].[dbo].[AdventureWorks Sales Data]
        FROM [CustomAdventureWorksDW].[dbo].[AdventureWorks Sales Data 2020]
        UNION ALL
        SELECT * FROM [CustomAdventureWorksDW].[dbo].[AdventureWorks Sales Data 2021]
        UNION ALL
        SELECT * FROM [CustomAdventureWorksDW].[dbo].[AdventureWorks Sales Data 2022]

    COMMIT TRANSACTION T8SDCD;
END TRY

BEGIN CATCH
    -- If an exception occurs, roll back the transaction
    IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION T8SDCD;
            -- Optionally, you can log or handle the error here
            -- For example:
            PRINT 'Error occurred: ' + ERROR_MESSAGE();
        END
END CATCH;

v. AdventureWorks Product Lookup:

create table

bulk import

Faced Error:

(0 rows affected) Error occurred: Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

data got pushed and hence data types failing

Solution:

[Link]

Text qualifier (write only ")

now good:

In T-SQL query: not solved though used FIELDQUOTE = '"', as hint

Another Error:

Solution:

using import flat file wizard

success using import flat file wizard:

Data:

vi. AdventureWorks Product Categories Lookup

create table

BEGIN TRY

    BEGIN TRANSACTION T11PCT; -- Start the transaction

    -- Your main content of the script here

        IF OBJECT_ID(N'[CustomAdventureWorksDW].[dbo].[AdventureWorks Product Categories Lookup]', N'U') IS NOT NULL
            BEGIN
                PRINT('[CustomAdventureWorksDW].[dbo].[AdventureWorks Product Categories Lookup] EXISTS, Dropped')
                DROP TABLE [CustomAdventureWorksDW].[dbo].[AdventureWorks Product Lookup]
            END

        ELSE
            BEGIN
                PRINT('[CustomAdventureWorksDW].[dbo].[AdventureWorks Product Categories Lookup] DOES NOT EXIST, Created')
                CREATE TABLE [CustomAdventureWorksDW].[dbo].[AdventureWorks Product Categories Lookup] (
                    [ProductCategoryKey] varchar(50),
                    [CategoryName] varchar(50)
                    )
            END

    -- If no exception occurs, commit the transaction
    COMMIT TRANSACTION T11PCT;

END TRY

BEGIN CATCH
    -- If an exception occurs, roll back the transaction
    IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION T11PCT;
            -- Optionally, you can log or handle the error here
            -- For example:
            PRINT 'Error occurred: ' + ERROR_MESSAGE();
        END
END CATCH;

bulk import

BEGIN TRY

    BEGIN TRANSACTION T12PCBI; -- Start the transaction

    -- Your main content of the script here
        -- import the file
        BULK INSERT [CustomAdventureWorksDW].[dbo].[AdventureWorks Product Categories Lookup]
        FROM 'path\AdventureWorks Product Categories Lookup.csv'
        WITH (
            FORMAT='CSV',
            FIRSTROW=2,
            FIELDTERMINATOR = ',', 
            ROWTERMINATOR = '0x0a'
            )

    -- If no exception occurs, commit the transaction
    COMMIT TRANSACTION T12PCBI;

END TRY

BEGIN CATCH
    -- If an exception occurs, roll back the transaction
    IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION T12PCBI;
            -- Optionally, you can log or handle the error here
            -- For example:
            PRINT 'Error occurred: ' + ERROR_MESSAGE();
        END
END CATCH;

vi. AdventureWorks Product Subcategories Lookup

create table

BEGIN TRY

    BEGIN TRANSACTION T13PSCT; -- Start the transaction

    -- Your main content of the script here

        IF OBJECT_ID(N'[CustomAdventureWorksDW].[dbo].[AdventureWorks Product Subcategories Lookup]', N'U') IS NOT NULL
            BEGIN
                PRINT('[CustomAdventureWorksDW].[dbo].[AdventureWorks Product Subcategories Lookup] EXISTS, Dropped')
                DROP TABLE [CustomAdventureWorksDW].[dbo].[AdventureWorks Product Subcategories Lookup]
            END

        ELSE
            BEGIN
                PRINT('[CustomAdventureWorksDW].[dbo].[AdventureWorks Product Subcategories Lookup] DOES NOT EXIST, Created')
                CREATE TABLE [CustomAdventureWorksDW].[dbo].[AdventureWorks Product Subcategories Lookup] (
                    [ProductSubcategoryKey] int,
                    [SubcategoryName] varchar(50),
                    [ProductCategoryKey] int
                    )
            END

    -- If no exception occurs, commit the transaction
    COMMIT TRANSACTION T13PSCT;

END TRY

BEGIN CATCH
    -- If an exception occurs, roll back the transaction
    IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION T13PSCT;
            -- Optionally, you can log or handle the error here
            -- For example:
            PRINT 'Error occurred: ' + ERROR_MESSAGE();
        END
END CATCH;

bulk import

BEGIN TRY

    BEGIN TRANSACTION T14PSCBI; -- Start the transaction

    -- Your main content of the script here
        -- import the file
        BULK INSERT [CustomAdventureWorksDW].[dbo].[AdventureWorks Product Subcategories Lookup]
        FROM 'path\AdventureWorks Product Subcategories Lookup.csv'
        WITH (
            FORMAT='CSV',
            FIRSTROW=2,
            FIELDTERMINATOR = ',', 
            ROWTERMINATOR = '0x0a'
            )

    -- If no exception occurs, commit the transaction
    COMMIT TRANSACTION T14PSCBI;

END TRY

BEGIN CATCH
    -- If an exception occurs, roll back the transaction
    IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION T14PSCBI;
            -- Optionally, you can log or handle the error here
            -- For example:
            PRINT 'Error occurred: ' + ERROR_MESSAGE();
        END
END CATCH;

vii. AdventureWorks Customer Lookup

create table

BEGIN TRY

    BEGIN TRANSACTION T15CULT; -- Start the transaction

    -- Your main content of the script here

        IF OBJECT_ID(N'[CustomAdventureWorksDW].[dbo].[AdventureWorks Customer Lookup]', N'U') IS NOT NULL
            BEGIN
                PRINT('[CustomAdventureWorksDW].[dbo].[AdventureWorks Customer Lookup] EXISTS, Dropped')
                DROP TABLE [CustomAdventureWorksDW].[dbo].[AdventureWorks Customer Lookup]
            END

        ELSE
            BEGIN
                PRINT('[CustomAdventureWorksDW].[dbo].[AdventureWorks Customer Lookup] DOES NOT EXIST, Created')
                CREATE TABLE [CustomAdventureWorksDW].[dbo].[AdventureWorks Customer Lookup] (
                    [CustomerKey] int NOT NULL,
                    [Prefix] varchar(50),
                    [FirstName] varchar(50),
                    [LastName] varchar(50),
                    [BirthDate] date,
                    [MaritalStatus] varchar(50),
                    [Gender] varchar(50),
                    [EmailAddress] varchar(50),
                    [AnnualIncome] int,
                    [TotalChildren] int,
                    [EducationLevel] varchar(50),
                    [Occupation] varchar(50),
                    [HomeOwner] varchar(50)
                    )
            END

    -- If no exception occurs, commit the transaction
    COMMIT TRANSACTION T15CULT;

END TRY

BEGIN CATCH
    -- If an exception occurs, roll back the transaction
    IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION T15CULT;
            -- Optionally, you can log or handle the error here
            -- For example:
            PRINT 'Error occurred: ' + ERROR_MESSAGE();
        END
END CATCH;

bulk import

BEGIN TRY

    BEGIN TRANSACTION T16CULBI; -- Start the transaction

    -- Your main content of the script here
        -- import the file
        BULK INSERT [CustomAdventureWorksDW].[dbo].[AdventureWorks Customer Lookup]
        FROM 'path\AdventureWorks Customer Lookup.csv'
        WITH (
            FORMAT='CSV',
            FIRSTROW=2,
            FIELDTERMINATOR = ',', 
            ROWTERMINATOR = '0x0a'
            )

    -- If no exception occurs, commit the transaction
    COMMIT TRANSACTION T16CLBI;

END TRY

BEGIN CATCH
    -- If an exception occurs, roll back the transaction
    IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION T16CULBI;
            -- Optionally, you can log or handle the error here
            -- For example:
            PRINT 'Error occurred: ' + ERROR_MESSAGE();
        END
END CATCH;

viii. AdventureWorks Calendar Lookup

create table

BEGIN TRY

    BEGIN TRANSACTION T17CALT; -- Start the transaction

    -- Your main content of the script here

        IF OBJECT_ID(N'[CustomAdventureWorksDW].[dbo].[AdventureWorks Calendar Lookup]', N'U') IS NOT NULL
            BEGIN
                PRINT('[CustomAdventureWorksDW].[dbo].[AdventureWorks Calendar Lookup] EXISTS, Dropped')
                DROP TABLE [CustomAdventureWorksDW].[dbo].[AdventureWorks Calendar Lookup]
            END

        ELSE
            BEGIN
                PRINT('[CustomAdventureWorksDW].[dbo].[AdventureWorks Calendar Lookup] DOES NOT EXIST, Created')
                CREATE TABLE [CustomAdventureWorksDW].[dbo].[AdventureWorks Calendar Lookup] (
                    [Date] date
                    )
            END

    -- If no exception occurs, commit the transaction
    COMMIT TRANSACTION T17CALT;

END TRY

BEGIN CATCH
    -- If an exception occurs, roll back the transaction
    IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION T17CALT;
            -- Optionally, you can log or handle the error here
            -- For example:
            PRINT 'Error occurred: ' + ERROR_MESSAGE();
        END
END CATCH;

bulk import

BEGIN TRY

    BEGIN TRANSACTION T18CALBI; -- Start the transaction

    -- Your main content of the script here
        -- import the file
        BULK INSERT [CustomAdventureWorksDW].[dbo].[AdventureWorks Calendar Lookup]
        FROM 'path\AdventureWorks Calendar Lookup.csv'
        WITH (
            FORMAT='CSV',
            FIRSTROW=2,
            FIELDTERMINATOR = ',', 
            ROWTERMINATOR = '0x0a'
            )

    -- If no exception occurs, commit the transaction
    COMMIT TRANSACTION T18CALBI;

END TRY

BEGIN CATCH
    -- If an exception occurs, roll back the transaction
    IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION T18CALBI;
            -- Optionally, you can log or handle the error here
            -- For example:
            PRINT 'Error occurred: ' + ERROR_MESSAGE();
        END
END CATCH;

ix. AdventureWorks Territory Lookup

create table

BEGIN TRY

    BEGIN TRANSACTION T19TLT; -- Start the transaction

    -- Your main content of the script here

        IF OBJECT_ID(N'[CustomAdventureWorksDW].[dbo].[AdventureWorks Territory Lookup]', N'U') IS NOT NULL
            BEGIN
                PRINT('[CustomAdventureWorksDW].[dbo].[AdventureWorks Territory Lookup] EXISTS, Dropped')
                DROP TABLE [CustomAdventureWorksDW].[dbo].[AdventureWorks Territory Lookup]
            END

        ELSE
            BEGIN
                PRINT('[CustomAdventureWorksDW].[dbo].[AdventureWorks Territory Lookup] DOES NOT EXIST, Created')
                CREATE TABLE [CustomAdventureWorksDW].[dbo].[AdventureWorks Territory Lookup] (
                    [SalesTerritoryKey] int NOT NULL,
                    [Region] varchar(50),
                    [Country] varchar(50),
                    [Continent] varchar(50)
                    )
            END

    -- If no exception occurs, commit the transaction
    COMMIT TRANSACTION T19TLT;

END TRY

BEGIN CATCH
    -- If an exception occurs, roll back the transaction
    IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION T19TLT;
            -- Optionally, you can log or handle the error here
            -- For example:
            PRINT 'Error occurred: ' + ERROR_MESSAGE();
        END
END CATCH;

bulk import

BEGIN TRY

    BEGIN TRANSACTION T20TLBI; -- Start the transaction

    -- Your main content of the script here
        -- import the file
        BULK INSERT [CustomAdventureWorksDW].[dbo].[AdventureWorks Territory Lookup]
        FROM 'path\AdventureWorks Territory Lookup.csv'
        WITH (
            FORMAT='CSV',
            FIRSTROW=2,
            FIELDTERMINATOR = ',', 
            ROWTERMINATOR = '0x0a'
            )

    -- If no exception occurs, commit the transaction
    COMMIT TRANSACTION T20TLBI;

END TRY

BEGIN CATCH
    -- If an exception occurs, roll back the transaction
    IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION T20TLBI;
            -- Optionally, you can log or handle the error here
            -- For example:
            PRINT 'Error occurred: ' + ERROR_MESSAGE();
        END
END CATCH;

x. AdventureWorks Returns Data

create table

BEGIN TRY

    BEGIN TRANSACTION T21RDT; -- Start the transaction

    -- Your main content of the script here

        IF OBJECT_ID(N'[CustomAdventureWorksDW].[dbo].[AdventureWorks Returns Data]', N'U') IS NOT NULL
            BEGIN
                PRINT('[CustomAdventureWorksDW].[dbo].[AdventureWorks Returns Data] EXISTS, Dropped')
                DROP TABLE [CustomAdventureWorksDW].[dbo].[AdventureWorks Returns Data]
            END

        ELSE
            BEGIN
                PRINT('[CustomAdventureWorksDW].[dbo].[AdventureWorks Returns Data] DOES NOT EXIST, Created')
                CREATE TABLE [CustomAdventureWorksDW].[dbo].[AdventureWorks Returns Data] (
                    [ReturnDate] date,
                    [TerritoryKey] int NOT NULL,
                    [ProductKey] int NOT NULL,
                    [ReturnQuantity] int
                    )
            END

    -- If no exception occurs, commit the transaction
    COMMIT TRANSACTION T21RDT;

END TRY

BEGIN CATCH
    -- If an exception occurs, roll back the transaction
    IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION T21RDT;
            -- Optionally, you can log or handle the error here
            -- For example:
            PRINT 'Error occurred: ' + ERROR_MESSAGE();
        END
END CATCH;

bulk import

BEGIN TRY

    BEGIN TRANSACTION T22RDBI; -- Start the transaction

    -- Your main content of the script here
        -- import the file
        BULK INSERT [CustomAdventureWorksDW].[dbo].[AdventureWorks Returns Data]
        FROM 'path\AdventureWorks Returns Data.csv'
        WITH (
            FORMAT='CSV',
            FIRSTROW=2,
            FIELDTERMINATOR = ',', 
            ROWTERMINATOR = '0x0a'
            )

    -- If no exception occurs, commit the transaction
    COMMIT TRANSACTION T22RDBI;

END TRY

BEGIN CATCH
    -- If an exception occurs, roll back the transaction
    IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION T22RDBI;
            -- Optionally, you can log or handle the error here
            -- For example:
            PRINT 'Error occurred: ' + ERROR_MESSAGE();
        END
END CATCH;

2. Ingest Data into Power BI

4. Transformations

i. AdventureWorks Sales Data

no transformations

ii. AdventureWorks Product Lookup

Changed to Currency

I changed the column data types in sql server itself as I want to use Direct query instead of import.

RemoveColumns

ProductSize

AddColumn

based on a condition before hypen delimiter on column ProductSKU

column rename

SKU Type

change data type

Text

Replace

"0", to "NA" for column ProductStyle

AddColumn

based on a condition ProductPrice * 0.9

Transformation done for Product lookup:

iii. AdventureWorks Product Categories Lookup

change column type

ProductCategoryKey to Int

iv. AdventureWorks Product Subcategories Lookup

None for me.

v. AdventureWorks Customer Lookup

RemoveRowsWithErrors:

CustomerKey

Filter rows

CustomerKey remove nulls, remove empty

Capital S starting letter of the word

Prefix, Firstname, Lastname

AddColumn

Combine Prefix, FirstName, LastName

addcolumn

extract from emailaddress column and rename to Domain Name

vi. AdventureWorks Calendar Lookup

add columns

vii. AdventureWorks Territory Lookup

None

viii. AdventureWorks Returns Data

None

ix. Rolling Calendar

Blank Query and enter 01-01-2023

right click source and choose insert step after to get custom1

x. Measure Table

select Enter data option and do not type any info.

Note:

remove the column the measure table.

5. Modeling

Building relationships

6. Measures

i. Calendar Lookup

Mark date column as date column.

change format of date column to short date.

Date Hierarchy

Calculated columns:

Day of Week

Return type: 2, week begins on Monday (1) and ends on Sunday (7).

Day of Week = 
    WEEKDAY(
        'Calendar Lookup'[Date],
        2
    )

Month Number (SWITCH)

Month Number (SWITCH) = 
    SWITCH(
            'Calendar Lookup'[Month Name],
            "January", "1",
            "February","2",
            "March","3",
            "April","4",
            "May","5",
            "June","6",
            "July","7",
            "August","8",
            "September","9",
            "October","10",
            "November","11",
            "December","12"
    )

Month Short

Month Short = 
    UPPER(
        LEFT(
            'Calendar Lookup'[Month Name],
            3
        )
    )

Weekend

Weekend = 
    SWITCH(
        TRUE(),
        'Calendar Lookup'[Day of Week] IN {6,7}, "Weekend",
        "Weekday"
    )

ii. Customer Lookup

Don't summarize the Annual income column, total children column.

change format of Birthdate column to short.

Calculated columns:

Birth Year

Birth Year = 
    YEAR('Customer Lookup'[BirthDate]
    )

Customer Full Name (CC)

Customer Full Name (CC) = 
'Customer Lookup'[Prefix]&" "&'Customer Lookup'[FirstName]&" "&'Customer Lookup'[LastName]

Is Parent?

Is Parent? = 
    SWITCH(
        TRUE(),
        'Customer Lookup'[TotalChildren] > 0, "Yes",
        "No"
    )

Customer Priority

Customer Piority = 
   SWITCH(
        TRUE(),
        'Customer Lookup'[AnnualIncome] > 100000 && 'Customer Lookup'[Is Parent?] = "Yes", "Priority",
        "Standard"
   )

Education Category

Education Category =
    SWITCH(
        'Customer Lookup'[EducationLevel],
        "High School", "High School",
        "Partial High School","High School",
        "Bachelors","Undergrad",
        "Partial College","Undergrad",
        "Graduate Degree","Graduate"
    )

Income Level

Income Level = 
    SWITCH(
        TRUE(),
        'Customer Lookup'[AnnualIncome] >= 150000, "Very High",
        'Customer Lookup'[AnnualIncome] >= 100000, "High",
        'Customer Lookup'[AnnualIncome] >= 50000, "Average",
        "Low"
    )

iii. Customer Metric Selection calculated table

perform this after building all measures in measure table.

Modelling/parameters/new parameters/fields/select

Customer Metric Selection = {
    ("Total Customers", NAMEOF('Measure Table'[Total Customers]),0),
    ("Revenue per Customer", NAMEOF('Measure Table'[Average Revenue per Customer]),1)
}

rename columns

Customer Metric Selection

hide in report view

Customer Metric Selection Fields

Customer Metric Selection Order

iv. Price Adjustment (%) calculated table

perform this after building all measures in measure table.

Modelling/parameters/new parameters/numeric range/select

Price Adjustment (%) = GENERATESERIES(-1, 1, 0.1)

rename & dont summarize

Measure:

Price Adjustment (%) Value

Price Adjustment (%) Value = 
    SELECTEDVALUE(
        'Price Adjustment (%)'[Price Adjustment (%)],
        0
        )

v. Product Lookup

hide in report view

ProductSubcategoryKey

Calculated column:

Price Point

Price Point = 
    SWITCH(
        TRUE(),
        'Product Lookup'[ProductPrice] > 500, "High",
        'Product Lookup'[ProductPrice] > 100, "Mid-Range",
        "Low"
    )

SKU Category

SKU Category = 
    LEFT(
        'Product Lookup'[SKU Type],
        SEARCH(
            "-",
            'Product Lookup'[SKU Type]
        ) -1
    )

vi. Product Categories Lookup

none

vii. Product Subcategories Lookup

hide in report view

ProductCategoryKey

viii. Product Metric Selection calculated table

perform this after building all measures in measure table.

Modelling/parameters/new parameters/fields

Product Metric Selection = {
    ("Orders", NAMEOF('Measure Table'[Total Orders]), 0),
    ("Revenue", NAMEOF('Measure Table'[Total Revenue]), 1),
    ("Profit", NAMEOF('Measure Table'[Total Profit]), 2),
    ("Returns", NAMEOF('Measure Table'[Total Returns]), 3),
    ("Return %", NAMEOF('Measure Table'[Return Rate]), 4)
}

hide in report view

Product Metric Selection Fields
Product Metric Selection Order

rename them

Sort Product Metric Selection by Product Metric Selection Order

ix. Returns Data

hide in report view

ProductKey
ReturnDate
TerritoryKey

x. Rolling Calendar

None

xi. Sales Data

hide in report view

CustomerKey
OrderDate
ProductKey
StockDate
TerritoryKey

Calculated column:

Quantity Type

Quantity Type = 
    SWITCH(
        TRUE(),
        'Sales Data'[OrderQuantity] > 1, "Multiple Items",
        "Single Item"
    )

Retail Price

set currency format.

Retail Price = 
    RELATED(
        'Product Lookup'[ProductPrice]
        )

Revenue

Revenue = 
    'Sales Data'[OrderQuantity] * 'Sales Data'[Retail Price]

xii. Territory Lookup

configure data categories:

continent
country

add Territory Hierarchy

xiii. Measure Table

Total Revenue

Add the total revenue measure to the measure table and go to transform and delete the single column.

Total Revenue = 
    SUMX(
        'Sales Data',
        'Sales Data'[OrderQuantity] 
        * 
        RELATED(
            'Product Lookup'[ProductPrice]
        )
    )

YTD Revenue

YTD Revenue = 
    CALCULATE(
        [Total Revenue],
        DATESYTD(
            'Calendar Lookup'[Date]
        )
    )

Total Orders

Total Orders = 
  DISTINCTCOUNT(
    'Sales Data'[OrderNumber]
  )

Weekend Orders

Weekend Orders = 
    CALCULATE(
        [Total Orders],
        'Calendar Lookup'[Weekend] = "Weekend"
    )

Total Returns

Total Returns = 
 COUNT(
    'Returns Data'[ReturnQuantity]
 )

Total Cost

Total Cost = 
    SUMX(
        'Sales Data',
        'Sales Data'[OrderQuantity]
        *
        RELATED(
            'Product Lookup'[ProductCost]
            )
    )

Total Profit

Total Profit = 
    [Total Revenue] - [Total Cost]

Total Customers

Total Customers = 
DISTINCTCOUNT(
    'Sales Data'[CustomerKey]
)

Previous Month Revenue

Previous Month Revenue = 
    CALCULATE(
        [Total Revenue],
        DATEADD(
            'Calendar Lookup'[Date],
            -1,
            MONTH
        )
    )

Revenue Target

Revenue Target = 
    [Previous Month Revenue] * 1.1

Revenue Target Gap

Revenue Target Gap = 
    [Total Revenue] - [Revenue Target]

Quantity Returned

Quantity Returned = 
    SUM(
        'Returns Data'[ReturnQuantity]
    )

Quantity Sold

Quantity Sold = 
    SUM(
        'Sales Data'[OrderQuantity]
    )

Return Rate

Return Rate =
    DIVIDE(
        [Quantity Returned],
        [Quantity Sold],
        "No Sales"
    )

Previous Month Profit

Previous Month Profit = 
    CALCULATE(
        [Total Profit],
        DATEADD(
            'Calendar Lookup'[Date],
            -1,
            MONTH
        )
    )

Profit Target

Profit Target = 
    [Previous Month Profit] * 1.1

Profit Target Gap

Profit Target Gap = 
    [Total Profit] - [Profit Target]

Previous Month Returns

Previous Month Returns = 
    CALCULATE(
        [Total Returns],
        DATEADD(
            'Calendar Lookup'[Date],
            -1,
            MONTH
        )
    )

Previous Month Orders

Previous Month Orders = 
    CALCULATE(
        [Total Orders],
        DATEADD(
            'Calendar Lookup'[Date],
            -1,
            MONTH
        )
    )

Order Target

Order Target = 
    [Previous Month Orders] * 1.1

Order Target Gap

Order Target Gap = 
    [Total Orders] - [Order Target]

Bulk Orders

Bulk Orders = 
    CALCULATE(
        [Total Orders],
        'Sales Data'[OrderQuantity] > 1
    )

Bike Sales

Bike Sales = 
    CALCULATE(
        [Quantity Sold],
        'Product Categories Lookup'[CategoryName] = "Bikes"
    )

Bike Returns

Bike Returns = 
    CALCULATE(
        [Quantity Returned],
        'Product Categories Lookup'[CategoryName] = "Bikes"
    )

Bike Return Rate

Bike Return Rate = 
    CALCULATE(
        [Return Rate],
        'Product Categories Lookup'[CategoryName] = "Bikes"
    )

Average Revenue per Customer

Average Revenue per Customer = 
    DIVIDE(
        [Total Revenue],
        [Total Customers]
    )

Average Retail Price

Average Retail Price = 
    AVERAGE(
        'Product Lookup'[ProductPrice]
    )

Overall Average Price

Overall Average Price = 
    CALCULATE(
        [Average Retail Price],
        ALL(
            'Product Lookup'
        )
    )

ALL() - will ignore/removes any applied filters.

High Ticket Orders

High Ticket Orders = 
    CALCULATE(
        [Total Orders],
        FILTER(
            'Product Lookup',
            'Product Lookup'[ProductPrice] > [Overall Average Price]
        )
    )

All Returns

All Returns = 
    CALCULATE(
        [Total Returns],
        ALL(
            'Returns Data'
        )
    )

All Orders

All Orders = 
    CALCULATE(
        [Total Orders],
        ALL(
            'Sales Data'
        )
    )

Adjusted Price

Adjusted Price = 
    [Average Retail Price] * (1 + 'Price Adjustment (%)'[Price Adjustment (%) Value])

Adjusted Revenue

Adjusted Revenue = 
    SUMX(
        'Sales Data',
        'Sales Data'[OrderQuantity]
        *
        [Adjusted Price]
    )

Adjusted Profit

Adjusted Profit = 
    [Adjusted Revenue] - [Total Cost]

90-day Rolling Profit

90-day Rolling Profit = 
    CALCULATE(
        [Total Profit],
        DATESINPERIOD(
            'Calendar Lookup'[Date],
            LASTDATE(
                'Calendar Lookup'[Date]
            ),
            -90,
            DAY
        )
    )

10-day Rolling Revenue

10-day Rolling Revenue = 
    CALCULATE(
        [Total Revenue],
        DATESINPERIOD(
            'Calendar Lookup'[Date],
            MAX(
                'Calendar Lookup'[Date]
            ),
            -10,
            DAY
        )
    )

% of All Returns

% of All Returns = 
    DIVIDE(
        [Total Returns],
        [All Returns]
    )

% of All Orders

% of All Orders = 
    DIVIDE(
        [Total Orders],
        [All Orders]
    )

7. Visualization

Exec Dashboard page

Top 10 products Matrix visual:

data bars

KPI Monthly Returns:

Distance to goal - distance direction: decreasing is positive. Next

trend axis - direction: Low is good

Bookmarks

slicer

set action for button by assigning action

Category tooltip:

hide Category Tooltip page

set canvas background

Map page:

select image in selection panel and toggle action and set type to page navigation and designation to Map.

Product Detail Page:

Gauge Monthly vs Target visual:

color conditional format for callout value

slicer Price adjustment:

make sure you create new parameters by Modelling/parameters/new parameters/numeric range/select

then only you will get single value in slicer settings.

Report summary:

right click visual and select summarize

Current month orders value

Customer Detail page

add this text to i button in style settings:

Among customers in skilled manual roles in 2022, Ruben Suarez drove the most revenue at $4,683.

Q&A page

Decomposition Tree page:

Key Influencers page:

Final dashboard:

Completed successfully the Project AW dashboard.

Conclusion

Learning Objectives,

Ingest data into SQL
Connect and transform the raw source data
Design and build a relational model
Analyze the data with calculated columns & DAX measures
Design custom, interactive reports and dashboards

Ingest data into SQL & Build AdventureWorks analytics dashboard in Microsoft Power BI

Table of contents

Power BI Desktop Benchmark Assessment

1. Get Data into SQL Server

i. AdventureWorks Sales Data 2020

ii. AdventureWorks Sales Data 2021:

iii AdventureWorks Sales Data 2022

iv. Combining into sales data:

v. AdventureWorks Product Lookup:

vi. AdventureWorks Product Categories Lookup

vi. AdventureWorks Product Subcategories Lookup

vii. AdventureWorks Customer Lookup

viii. AdventureWorks Calendar Lookup

ix. AdventureWorks Territory Lookup

x. AdventureWorks Returns Data

2. Ingest Data into Power BI

4. Transformations

i. AdventureWorks Sales Data

ii. AdventureWorks Product Lookup

iii. AdventureWorks Product Categories Lookup

iv. AdventureWorks Product Subcategories Lookup

v. AdventureWorks Customer Lookup

vi. AdventureWorks Calendar Lookup

vii. AdventureWorks Territory Lookup

viii. AdventureWorks Returns Data

ix. Rolling Calendar

x. Measure Table

5. Modeling

6. Measures

i. Calendar Lookup

ii. Customer Lookup

iii. Customer Metric Selection calculated table

iv. Price Adjustment (%) calculated table

v. Product Lookup

vi. Product Categories Lookup

vii. Product Subcategories Lookup

viii. Product Metric Selection calculated table

ix. Returns Data

x. Rolling Calendar

xi. Sales Data

xii. Territory Lookup

xiii. Measure Table

7. Visualization

Exec Dashboard page

Map page:

Product Detail Page:

Customer Detail page

Q&A page

Decomposition Tree page:

Key Influencers page:

Final dashboard:

Source: [Link]

Author: Dheeraj. Yss

Connect with me:

Did you find this article valuable?