Explore the Classic Excel Dashboard

Problem:

VanArsdel is a company that manufactures and sells sporting goods. The company has offices in the United States (US) and several other countries. Its sales comprise of US sales and International sales. VanArsdel’s sales come from its owned manufactured products, as well as other manufacturers’ products.

VanArsdel’s Canada’s office has sent you their sales dashboard for the year 2015 up to the end of June, in form of an Excel file.

The company uses a logical operator in the column/field named isVanArsdel to track whether sales come from their manufactured products or from other manufacturers’ products. The logical operators used includes a “Y” (for Yes) to represent a record (a row in the table) of a sales transaction for a VanArsdel manufactured product and a “N” (for No) to represent a record of a sales transaction for an other manufacturers’ product.

You will use this file to review VanArsdel’s sales in Canada.

data :

You want to analyze the percentage of the VanArsdel sales in Canada that come from VanArsdel’s manufactured goods, by each State (Province). While the chart “Sales by State” shows an overview of this information, you want to show the exact percentage (to two decimal places) of VanArsdel’s sales from VanArsdel’s manufactured goods. Remember – VanArsdel sells both their own manufactured goods as well as goods from other manufacturers. Finally, you want to highlight if VanArsdel’s “share” of goods sold by manufacturer for that state is less than the overall VanArsdel’s “share” for the country.

  1. Create a pivot table to achieve this task. 
  2. Use the State field from the Sales table for RowsisVanArsdel field for Columns, and Sum of Revenue for Values.
  3. Set Show values as % of Row Total – This will provide you with two percentages for each state (row) that sum to 100%: the percent of revenue from non-VanArsdel manufactured goods sold within that state and the percent of revenue from VanArsdel manufactured goods sold within that state.

Excel Data Model:

If you look closely by downloading the sheet you will find the excel to be blank but data resides inside it .

Go to data tab click on manage data model it will open up the power pivot option and you can see the data resides inside sheet.

In diagram view you can see how different tables are interconnected .

You can see relations between table from here:

Now You Can create various calculated field for analysis in your power pivot for example create cost revenue and margin in the sheet

for real time view you can keep refresh your feed to check for updates

What is DAX?

Data Analysis Expressions (DAX) is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. Stated more simply, DAX helps you create new information from data already in your model.

Why is DAX so important?

It’s easy to create a workbook and import some data into it. You can even create pivot tables or pivot charts that display important information without using any DAX formulas. But, what if you need to analyze critical sales data across several product categories and for different date ranges? Or, you need combine important inventory data from several tables in different data sources? DAX formulas provide this capability and many other important capabilities as well. Learning how to create effective DAX formulas will help you get the most out of your data. When you get the information you need, you can begin to solve real business problems that affect your bottom line. This is Business Intelligence, and DAX will help you get there.

You might already be familiar with creating formulas in Microsoft Excel. That knowledge will be helpful in understanding DAX, but even if you have no experience with Excel formulas, the concepts described here will help you get started creating DAX formulas and solving real-world BI problems right away.

Syntax

Let’s take a look at DAX formula syntax. Syntax includes the various elements that make up a formula, or more simply, how the formula is written.

For example, let’s assume we have a simple DAX formula used to create new data (named Margin) for each row in a calculated column, defined as follows: Margin:=[SalesAmount]-[TotalCost]. (formula text colors are for illustrative purposes only)

Caclulated column formula

This formula’s syntax includes the following elements:A. The equals sign operator (=) indicates the beginning of the formula, and when this formula is calculated it will return a result or value. All formulas that calculate a value will begin with an equals sign.
B. The referenced column [SalesAmount] contains the values we want to subtract from. A column reference in a formula is always surrounded by brackets []. Unlike Excel formulas which reference a cell, a DAX formula always references a column.
C. The subtraction (-) mathematical operator.
D. The referenced column [TotalCost] contains the values we want to subtract from values in the [SalesAmount] column.

When trying to understand how to read a DAX formula, it is often helpful to break down each of the elements into a language you think and speak every day. For example, you can read this formula as:

In this table, for each row in the Margin calculated column, calculate (=) a value by subtracting (-) values in the [ TotalCost ] column from values in the [ SalesAmount ] column.

Let’s take a look at another example. Let’s assume a DAX formula used to create a calculated field on a FactSales table, defined as follows: Sum of Sales Amount:=SUM(FactSales[SalesAmount])). (formula text colors are for illustrative purposes only)

Calculated column formula

This formula includes the following syntax elements:A. The calculated field name Sum of Sales Amount. Formulas for calculated fields can include the calculated field name, followed by a colon, followed by the calculation formula.
B. The equals sign operator (=) indicates the beginning of the calculation formula. When calculated, it will return a result.
C. The function SUM adds up all of the numbers in the [SalesAmount] column.
D. Parenthesis () surround one or more arguments. All functions require at least one argument. An argument passes a value to a function.
E. The referenced table FactSales.
F. The referenced column [SalesAmount] in the FactSales table. With this argument, the SUM function knows on which column to aggregate a SUM.

You can read this formula as:

For the calculated field named Sum of Sales Amount, calculate (=) the SUM of values in the [ SalesAmount ] column in the FactSales table.

When this calculated field is placed into the Values drop zone in a pivot table Field List, it calculates and returns values defined by each cell in the pivot table.

Notice there are a few things different about this formula compared to the formula we used for the Margin calculated column. In particular, we introduced a function, SUM. Functions are pre-written formulas that make it easier to do complex calculations and manipulations with numbers, dates, time, text, and more. You will learn more about DAX functions later.

Unlike the Margin calculated column earlier, you see the column [SalesAmount] was preceded by the table FactSales in which the column belongs. This is known as a fully qualified column name in that it includes the column name preceded by the table name. Columns referenced in the same table do not require the table name be included in the formula. This can make long formulas that reference many columns shorter and easier to read. However, it is good practice to always include the table name in your calculated field formulas, even when in the same table.

NOTE: If the name of a table contains spaces, reserved keywords, or disallowed characters, you must enclose the table name in single quotation marks. You must also enclose table names in quotation marks if the name contains any characters outside the ANSI alphanumeric character range, regardless of whether your locale supports the character set or not.

It is very important your formulas have the correct syntax. In most cases, if the syntax is not correct, a syntax error will be returned. In other cases, the syntax may be correct, but the values returned might not be what you are expecting. Power Pivot (and SQL Server Data Tools) includes IntelliSense; a feature used to create syntactically correct formulas by helping you select the correct elements.

TIP: Something really important to understand when typing an operator into a DAX formula is the data type in the arguments you are using. For example, if you were to type the following formula, = 1 & 2, the value returned would be a text value of “12”. This is because the ampersand (&) operator is for text concatenation. DAX interprets this formula to read: Calculate a result by taking the value 1 as text and add value 2 as text. Now, if you were to type = 1 + 2, DAX reads this formula as: Calculate a result by taking the numeric value 1 and adding the numeric value 2. The result is of course “3”, a numeric value. DAX calculates resultant values depending on the operator in the formula, not based on the data type of columns used in the argument.

In next post we will do hands on with data model in excel and explore further.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s