What is DAX in Power BI?
DAX stands for “Data Analysis & Expression”. DAX is a functional language which represents all the function in Excel & Power BI. In simple, we can call it “Function Representor”. Using DAX language we can create formulas in Power BI, if you are familiar with Excel formulasExcel FormulasThe term "basic excel formula" refers to the general functions used in Microsoft Excel to do simple calculations such as addition, average, and comparison. SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN Excel, MAX Excel, LEN Excel, TRIM Excel, IF Excel are the top ten excel formulas and functions. then understanding DAX formulas won’t be a difficult task.
Data Visualization is the need of the hour, and here we go, we have a “Microsoft Power BI” as the data visualization tool.
We have used MS Excel for numbers crunching, data analysis, and even for visualization as well. But now we have Power BI for visualization. With a simple drag and drop technique, we can create our visualization of the data. In this article, we will show you what DAX function is and how to start with it in Power BI.
Upload Data to Power Bi
You can download the workbook used in this example to practice along with us from the below link.
Below is the workbook data I am using.
This is a huge data, and you can download the workbook here.
- Now open Power Bi software and click on Get Data >>> Excel.
- Now the file picker window will come. Choose the downloaded file from the same location.
- As soon you choose the file, you can see worksheet names and tables available in the selected Excel workbook.
- In the workbook, I have named the table “sales,” so choose the same. On the right-hand side, you will see the preview and click on the “Upload” option.
- Now it will take some time to upload since the data is large. After the upload, you will see the below window.
- On the right-hand side of the window, you will see the “Fields” list, and this shows all the data headings in the selected table.
Now with this data, we need to play around by using DAX functions in Power BI.
DAX Calculation in Power BI
If you see the data, we have Total Units, Sale Price, and Cost Price only. We need to find out “Total Sales, Total Cost, Gross Profit” etc.…
First, let’s calculate “Total Sales.”
- Right-click on the table name “Sales” and choose “New Column.”
- Now the new column will open like the below.
- Now give a name to this as “Total Sales” and put an equal sign.
- Now apply the formula as “Total Units * Sales Price.”
As you can see above formula says “Sales [Total Units] * Sales [Sale Price].”
- Here “Sales” is the table name of the inserted table. Now, this new column can be viewed in the field list under “Sales Table.”
- Now take “Card” visualization and insert a newly created column i.e., Total Sales.
- Then the Card looks like as shown below:
- So, our first DAX calculation is working fine. Now again, right-click the “Sales” table name and choose “New Column” for “Total Cost.”
- For Total Cost, the formula is “Total Units * Cost Price.”
- Now the newly created column appears, inserted one more card for this DAX column as well.
- Now find the Gross Profit amount by using the formula Total Sales – Total Cost.
- Now arrive Gross ProfitGross ProfitGross Profit shows the earnings of the business entity from its core business activity i.e. the profit of the company that is arrived after deducting all the direct expenses like raw material cost, labor cost, etc. from the direct income generated from the sale of its goods and services. % by using this formula Gross Profit / Total Sales *100.
Ok, now we will see how to use some basic functions in Power BI DAX.
Functions in DAX Power BI
To store all the calculated fields, let us create a new measure for this. Click on the “Enter Data” option under the “Home” tab.
- Now create a table that comes up, for this gives a name as “Calculated Columns” and click on “Load.”
- Now we can see a new table name in the fields.
- First, we will use the SUM functionSUM FunctionThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges. to SUM some of the columns. As usual as right-click on the new table name and choose “New Measure.”
It pop-ups in Formula bar.
- Before we open the SUM function, we need to give a name for this column; since we are adding Total Sales, give the name as “SUM Total Sales” and then open the SUM function.
- As you can see, the argument of the SUM function is “Column Name,” i.e., which column you need to SUM. Now select the “Total Sales” column.
This also works the same way previous “Total Sales” works, but previous sales calculate the sales as Units * Sales Price, so since Total Sales already has sales amount, this SUM function simply adds all the values of the “Total Sales” column. Like this, we can use DAX functions in Power BI.
Note: Power BI DAX Function file can also be downloaded from the link below, and the final output can be viewed.
Things to Remember Here
- This is the beginner’s guide for DAX; in upcoming articles, we will more of these DAX functions in Power BI.
- Power BI is a visualization toolPower BI Is A Visualization ToolIn Power Bi, there are some built-in visuals for data visualization, as well as some custom visuals. Custom visuals are user-defined and allow users to use their own creativity in terms of how they want to present data. For example, one can use lines and dots in a flashing and floating manner to display changes in data over time. to visualize data.
Guide to Power BI DAX. Here we discuss how to use Data Analysis & Expression function to calculate DAX in Power BI with example. You may learn more about Power BI from the following articles –