## Understanding DAX through Sales Analysis

##### Description

This project focuses on understanding the workings of a particular DAX formula, namely CALCULATE( SUM(Sales[Amount]), ALLEXCEPT(Sales, Sales[Year], Sales[Region])). It aims to help the user clearly understand the functions and applications of the formula. The project is broken down into four self-explanatory, logical and independent units each focused on a specific aspect; ranging from understanding basic DAX formulas, to manipulating the specific formula, implementing it on a sales dataset, and finally summarizing the insights generated.

# Introduction to DAX and Basic Formulas

## Prerequisite

As we are using the DAX (Data Analysis Expressions) language, we will need Power BI or Power Pivot in Excel for implementing it.

Assuming you are using Power BI Desktop, you can download it from

Also, assuming that you already have a dataset prepared, let's begin our journey to learn more about DAX!

## Dataset Setup in Power BI

Open Power BI Desktop.

Click on the

`Home`

tab and select`Get Data`

.If your data is in a CSV format, select

`Text/CSV`

or choose the appropriate one (Excel, SQL Server, etc).Click on

`Connect`

, browse to your file, and click on`Open`

.Explore the

`Preview Data`

window and if everything looks fine, click on`Load`

.

The data is now loaded in Power BI and ready to be used for creating DAX formulas.

## Introduction to DAX

DAX is a collection of functions, operators, and constants, which can be used in a formula or expression, to calculate and return one or more values. These expressions define a set of values, but do not specify an order, or hierarchy, on those values.

When we create DAX formulas, it allows us to use it in:

- Calculated Columns: These are the columns that we add to the existing tables.
- Measures: These calculations are used in the context of the visual in the report.

## Basic DAX Formulas

Below, we'll implement a few basic DAX formulas to get you started.

### SUM

This DAX formula returns the sum of an entire column.

Syntax:

`SUM(`)

Example:

`Total Sales = SUM(Sales[Sales Amount])`

In the above query, `Sales`

is the table name, and `Sales Amount`

is the column name. This will return the total sales amount.

### AVERAGE

This DAX formula returns the average of a column.

Syntax:

`AVERAGE(`)

Example:

`Average Sales = AVERAGE(Sales[Sales Amount])`

### COUNT

Counts the number of rows in the target column.

Syntax:

`COUNT(`)

Example:

`Sales Count = COUNT(Sales[Sales ID])`

Make sure to replace `Sales`

and `Sales ID`

with your table name and column name.

These basic DAX formulas should be enough for this part of the project to give you an introduction and help you understand the basic calculations in DAX. And remember, you will need to create a new Measure and use the respective DAX formula in the `Formula`

section to use these in the Power BI reports.

Let's get down to implementing more complex scenarios in the next steps of the project!

# Understanding and Manipulating CALCULATE() function

## Exploring CALCULATE() Function

The `CALCULATE()`

function in DAX is a crucial command to create complex business solutions requiring changes in the calculation context i.e., to manipulate the context in a calculation.

Let's jump straight into understanding this function using a simple sales dataset.

**Dataset**

Assuming you have a dataset named "Sales" with the following data:

Product | SalesAmount | Month |
---|---|---|

P1 | 120 | Jan |

P2 | 150 | Jan |

P1 | 200 | Feb |

P2 | 500 | Feb |

P3 | 700 | Feb |

### Understanding CALCULATE()

The syntax for `CALCULATE()`

is as follows:

`CALCULATE ( `, , ....)

`<expression>`

is any DAX expression that returns a single scalar value.`<filter1>, <filter2>..`

are optional and represent expressions that define the filters.

Let's calculate the total sales amount.

`Total Sales = SUM ( Sales[SalesAmount] )`

We can further manipulate the above measure using `CALCULATE()`

. Let's calculate the sales amount for product "P1" only.

`Sales P1 = CALCULATE ( [Total Sales], Sales[Product] = "P1" )`

When you put this measure in a visual, it returns the total sales amount for P1 only, regardless of any other context being applied.

## Manipulating CALCULATE() Function

The power of `CALCULATE()`

lies in how it can change the context of a calculation. You can add, replace, or remove contexts by applying the relevant filters in `CALCULATE()`

expressions.

### Adding Context

Adding context means adding more filters to the existing context. Let's say, you want to calculate sales for product "P1" for the month of "Jan".

`Sales P1 Jan = CALCULATE ( [Total Sales], Sales[Product] = "P1", Sales[Month] = "Jan" )`

### Replacing Context

Replacing context means changing one or more aspects of the existing context. If you want to calculate sales of *any* product, but specifically for the month of "Feb", you would do:

`Sales Feb = CALCULATE ( [Total Sales], Sales[Month] = "Feb" )`

### Removing Context

The `ALL()`

function can be used within `CALCULATE()`

to ignore the context. For example, to calculate the total sales regardless of product or month:

`Total Sales Ignore Context = CALCULATE ( [Total Sales], ALL ( Sales ) )`

Remember, `CALCULATE()`

is the only function that can change the context in which the data is evaluated. Understanding and appropriately applying this function is vital to mastering DAX and effectively analyzing your data.

**Important:** `CALCULATE()`

changes context in an existing row context; it cannot create a row context on its own. This is essential to remember when you deal with complex expressions and scenarios.

Have fun exploring the other DAX functions!

# Implementation of Advanced Use of ALLEXCEPT() function

In this implementation, we're going to delve into particular ways the ALLEXCEPT() function can be utilized in Power BI using DAX on a sales dataset.

*Section 1*: Overview of ALLEXCEPT()

ALLEXCEPT() is a DAX function that filters all columns in a table except for the ones defined in the function call.

**General syntax:**

`ALLEXCEPT (, `, [], ...)*Section 2*: Working with ALLEXCEPT()

Let's say we have a sales dataset named 'SalesData' as below:

OrderID
Product
Category
Region
Sales
Quantity
1
A
Food
West
100
2
2
B
Drink
East
200
3
3
C
Food
North
150
4
4
D
Drink
South
300
5

**Use Case 1**: Calculate Total Sales By Category While Ignoring all Other Filters

**Use Case 1**: Calculate Total Sales By Category While Ignoring all Other Filters

```
```The function will calculate the total sales for each category while ignoring any other column filters in the dataset:

```
TotalSalesByCategory = CALCULATE(
SUM(SalesData[Sales]),
ALLEXCEPT(SalesData, SalesData[Category])
)
```

**Use Case 2**: Calculate Average Quantity Sold per Region Ignoring Other Filters

Here we calculate the average quantity sold for each region by ignoring any potential filters applied on other columns:

```
AvgQuantityByRegion = CALCULATE(
AVERAGE(SalesData[Quantity]),
ALLEXCEPT(SalesData, SalesData[Region])
)
```

**Use Case 3**: To Get Total Sales for Each Product in Each Category

If we wish to obtain sales per product for each category, ignoring filters on all other columns:

```
SalesPerProductPerCategory = CALCULATE(
SUM(SalesData[Sales]),
ALLEXCEPT(SalesData, SalesData[Product], SalesData[Category])
)
```

In these sample applications of the `ALLEXCEPT()`

function, all filters coming from other columns, except the ones listed, are removed during the evaluation of the expression. This functionality provides a powerful way to create more advanced calculations in Power BI.

`Implementation of DAX Formulas on Sales Dataset and Interpreting Results`

```
```Considering you've already acquainted yourself with basic DAX formulas, the CALCULATE() function, and advanced use of the ALLEXCEPT() function, let's now focus on applying some DAX calculations to a sales dataset:

**1. Loading the Dataset**

Should the sales data have been already loaded into Power BI, we would obtain the Table view of the dataset. This document will work with the following columns: `OrderDate`

, `Region`

, `TotalSales`

.

**2. Adding new DAX measures to the Sales dataset**

**(a) YEAR-TO-DATE SALES**

This measure calculates the cumulative total sales for the current year, up to the latest complete month.

```
YTD Sales =
TOTALYTD(
SUM('Sales'[TotalSales]),
'Sales'[OrderDate])
```

**(b) PREVIOUS YEAR SALES**

This measure calculates total sales for the previous year.

```
Previous Year Sales =
CALCULATE(
SUM('Sales'[TotalSales]),
DATEADD('Sales'[OrderDate], -1, YEAR))
```

**(c) YEAR OVER YEAR (YOY) SALES**

This measure calculates the percentage change in sales of the current year compared to the previous year.

```
YoY Sales =
'Sales'[YTD Sales] - 'Sales'[Previous Year Sales]
```

**(d) RUNNING TOTAL SALES**

This measure calculates the cumulative sales total, resetting at the start of each new year.

```
Running Total Sales =
CALCULATE(
SUM('Sales'[TotalSales]),
FILTER(
ALL('Sales'[OrderDate]),
'Sales'[OrderDate] <= MAX('Sales'[OrderDate])))
```

**3. Interpreting the Results**

**(a) YEAR-TO-DATE SALES**

This measure gives an understanding of the revenue up to a particular date in the current year. It offers an insight into whether sales are growing, stagnant, or declining over the year.

**(b) PREVIOUS YEAR-TO-DATE SALES**

This measure compares the current year's performance with the previous year's performance. It helps in identifying growth trends and planning future sales strategies.

**(c) YEAR OVER YEAR (YOY) SALES**

This measure provides the growth proportion of the current year sales compared to the previous year. It offers understanding into the business's progress concerning revenue generation from one year to the next.

**(d) RUNNING TOTAL SALES**

This measure is beneficial in identifying patterns or trends in cumulative sales. It will assist in understanding if there's a target that needs to be met, if the business is expected to reach it, or if sales strategies need tweaking to attain the goal.

As you further refine these calculations and use graphical representations, Power BI will allow you to visualize these sales KPIs effectively, drawing business insights more efficiently.

`More Code Generators`

` Apache Flink Code Generator Apache Pig Code Generator Azure Data Factory Code Generator C/C++ Code Generator CouchDB Code Generator DAX Code Generator Excel Code Generator Firebase Code Generator Google BigQuery Code Generator Google Sheets Code Generator GraphQL Code Generator Hive Code Generator Java Code Generator JavaScript Code Generator Julia Code Generator Lua Code Generator M (Power Query) Code Generator MATLAB Code Generator MongoDB Code Generator Oracle Code Generator PostgreSQL Code Generator Power BI Code Generator Python Code Generator R Code Generator Redis Code Generator Regex Code Generator Ruby Code Generator SAS Code Generator Scala Code Generator Shell Code Generator SPSS Code Generator SQL Code Generator SQLite Code Generator Stata Code Generator Tableau Code Generator VBA Code Generator`