AdventureWorks 1.0 - exploring cycling business (first attempt)

Business case

AdventureWorks is a global manufacturing company that produces cycling equipment and accessories. The management team needs to have the availability of an interactive dashboard to explore and understand business performance. They want to:

• track KPIs (sales, revenue, profit, returns)
• compare regional performance
• analyze product-level trend
• identify high-value customers

Project process

The raw data are csv files stored on-premises in folders. The files contain historical information about transactions, returns, products, customers, and sales territories.

The Power Query Editor inside Power BI is deployed to connect to the locally stored raw data. After QA & profiling to explore, clean, prepare, and shape the data the queries are loaded into Power BI’s “front-end”. The first step was building a relational data model. Then the necessary calculated columns and measures for visualizing the requested information in an interactive dashboard were created with DAX.

Key Questions

1.  What is the current year’s/month’s/week’s sales, revenue, profit, and returns?
   

2.  What product(categories) are revenue drivers?
   

3.  How do the regions perform in comparison?
   

4.  Who are the top 100 most valuable customers globally?
   

The Key Questions required answering sub-questions at a detailed level. To achieve this, 14 calculated columns and 34 measures were created using DAX. With that, the 4-page interactive dashboard you can access above was designed.

Learning-key-takeaways

Because this dashboard was created as part of the Microsoft Power BI Desktop 2023 course on the Maven Analytics platform, there was no thought process or analytical approach of my own regarding the creation of the dashboard. Instead, as a substitute, I selected two topics to highlight.

A dedicated Measure Table

By default, explicit measures are stored within the active table. To avoid spreading measures among all tables, or assigning all to a table of arbitrary choice, creating a dedicated table enables one to store them all in one place. A method to achieve this is the following:

• Step 1 - On the Home tab in the Report view click the ‘Enter data’ button which will create a table.
• Step 2 - Add a table Name and click ‘OK’ to load the table to the data model. This new table has only one column with the default name.
• Step 3 - Create a measure in that table or move a measure into it.
• Step 4 - Delete the initial column with the default name. This changes the icon of the table from a standard table to a calculated table. Also, the table is now positioned at the top of the data pane.

The behavior of CALCULATE

It took a while until I realized that CALCULATE has conditional behavior.

If cooperation with the report filter context is possible then CALCULATE accepts the report filter context and puts its CALCULATE filter on top of it.

If the report filter context conflicts with CALCULATE then CALCULATE takes the upper hand and fully ignores the report filter context.

Two examples of applying CALCULATE

Example 1: The measure ‘All Returns’ returns the number (count) of ‘Returns’ regardless of the report filter context. Nesting ALL removes any filters applied to the fact table.

Here CALCULATE will act as a “bully”.

Example 2: The measure ‘High Ticket Orders’ returns the number (count) of ‘Orders’ if the product price is above average.

Here CALCULATE will act cooperatively. CALCULATE adds a filter, the condition ‘product price is above average’ on top of the report filter context.

Note: Even if the product price would live in the ‘Sales Data’ table, the ‘Product Lookup’ table is the table of choice to apply the FILTER function on because, generally, a ‘Product Lookup’ table will consist of fewer records than a table with transactional data. And because FILTER is an iterator function evaluation of the Lookup table requires less engine capacity and will be quicker.