Scribd
Upload
0 views3 pages

DAX Tutorial

The document outlines steps for data analysis using DAX, including loading files, establishing relationships, and creating various measures such as Total Number of Units Sold and Count of Orders. It discusses formatting measures, creating matrices, and calculating profit margins, as well as the differences between aggregator and iterator functions. Additionally, it covers logical functions and the use of calculate functions to filter and analyze data based on specific criteria.

Uploaded by

ayeshairshadbusiness30
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
0 views3 pages

DAX Tutorial

The document outlines steps for data analysis using DAX, including loading files, establishing relationships, and creating various measures such as Total Number of Units Sold and Count of Orders. It discusses formatting measures, creating matrices, and calculating profit margins, as well as the differences between aggregator and iterator functions. Additionally, it covers logical functions and the use of calculate functions to filter and analyze data based on specific criteria.

Uploaded by

ayeshairshadbusiness30
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Data Analysis Expressions

Load these 3 files: Cookie Types, Customers, Order

Establish relation between these 3

Create a Measure:
Total Number of Units Sold = SUM(Orders[Units Sold])

Create a Matrix

Total Number of Units Sold & Names (in Customer Table)

Change the formatting of measure to zero decimal places and this formatting holds also add a comma

Change the name to Total number of cookies sold

Show delete from model

I want to know how many orders I have

Create a new measure called Count of Orders

Count of Orders = COUNTROWS(Orders)

Create a matrix of count of orders and customer names

Create a new measure of distinct customers

Distinct Customers = DISTINCTCOUNT(Orders[Customer ID])

Create a matrix to show Distinct Customers

Add comments in the measure

Create a new measure called Total Profit

Total Profit = SUM(Orders[Revenue])-SUM(Orders[Cost])

In the matrix of Distinct Customers, add Total Profit

Now, another way to show Total Profit


Using Quick Measure, use Subtraction, add Revenue in the field and then Cost in the second field and
measure in the name of Revenue minus Cost is being created, in the matrix show the same result

Third way is to add a column in the table view

Go the Table Tools, insert new column, named Profit

Profit = Orders[Revenue] - Orders[Cost]

Pros and Cons of creating new column and measure

Now in orders table, create a new measure called Profit Margin,

Profit Margin % = [Total Profit] / sum(Orders[Revenue])

New Matrix show


Cookie Type and Profit Margin, format to percentage,

Aggregator vs Iterator Functions


Aggregator functions work on entire column whereas iterator functions work on row by
row basis

Go to Cookie Types
Create new measure

Total Profit 2 = SUMX('Cookie Types','Cookie Types'[Units Sold]*('Cookie


Types'[Revenue Per Cookie]-'Cookie Types'[Cost Per Cookie]))
Show in a matrix same profit

Next we will see time and date functions similar to MS Excel


I want to see how many cookies I sold in each day of week

In the order table, I want to add new column,


Day of week = WEEKDAY(Orders[Date], 1)

Plot a graph bar between Day of Week vs Day of Week

Logical Functions
In orders table, go to table tools create new coloum, first find all product with chocolate name on it,
Has Choclate = FIND("Chocolate",Orders[Product],1,0)

Has Choclate = IF(FIND("Chocolate",Orders[Product],1,0)>0,"Has Chocolate","No


Chocolate")

Last we will see calculate functions, we can apply filters, criterion etc

How many products of chocolate chip in it with units order more than 500

Chocolate chip with over 500 units = CALCULATE(COUNTROWS(Orders),Orders[Units


Sold]>500,Orders[Product] == "Chocolate Chip")

You might also like