Power BI Variables: Simplifying Complex Formulas and Calculations

Variables in Power BI simplify complex formulas, making it easier to understand and manage calculations, improving overall efficiency and comprehension.

### Understanding Variables in Power BI: A Simplified Approach to Complex Calculations
* ⚙ In Power BI, variables are names that **store values**, making complex calculations **easier to understand and manage**.
* 📊 Variables can be defined using the `VAR` keyword, with the format **`VAR variable_name = calculation_expression`**.
* 📈 For example, to calculate profit percentage, variables for **profit**, **COGS**, and **sales** can be defined and used in the formula.
* 🔢 The formula for profit percentage using variables would be: `VAR profit = SUM(Profit), VAR cogs = SUM(Sales) - SUM(Profit), VAR profit_percentage = **profit / cogs** RETURN profit_percentage`.
* 💡 Using variables breaks down complex calculations into smaller, **more understandable parts**, making it **easier for beginners to follow**.
* 🔄 In contrast, the same calculation without variables would be written as: `**SUM(Profit) / (SUM(Sales) - SUM(Profit))**`, which can be **harder to interpret**, especially for those **new to Power BI**.
* 🔍 Variables improve code readability and maintainability, especially for **complex calculations that involve multiple steps**.
* 📈 By utilizing variables, Power BI users can streamline their DAX formulas, making them **more transparent and easier to troubleshoot**.

hi guys, in this session, we will discuss about the variables in Power BI. What variables will do for us in Power BI, let's see with some common examples. First, understand what a variable is. A variable is a kind of a name that stores your value. That means you can define any name which stores your data, and data is located in your memory. Any calculation can be performed which is going to affect your memory location. Now let's see, if I have a name 'a' and I have given here 255, meaning 'a' is the variable which holds your data 255. Anytime I'm going to write only 'a', it will result as 255. In the same way, if I'm going to write 'a b' is the name and defining 'b' as a variable and I am putting here a value like 12.24, meaning 'b' is the variable which holds 12.24 as a value. So, somewhere in a formula, we're going to write in Power BI Desktop, if we place 'a', meaning I am placing there 255. If we place 'b', meaning I'm placing there this value. You know what a profit

is, I'm going to show you using variables how we going to calculate a profit percentage. And for profit percent percentage, we need few components already meaning. In order to get a profit percentage, we need few components to have already meaning either I have profit or then I have cogs cost of good SS meanings whatever are buds associated with and what are the sales amount. Now I'm giving profit as a variable then just just write v. V is a shortcut of writing a variable in power VI. Once you write v a r at then profit that mean you are have defining a variable profit which holds what formula your sum of your sum of profit. This will give you the total total profit and this profit will have your sum of profit and this profit is your variable. Now I have sum of cogs meaning I am summing a total column of cogs which whatever value comes through this formula I have those as a single name cogs and Define here as a variable same for the sale sales and here I'm going to Define sum of sales meaning Sal is the

name of variable which I going to define as the sum of the whole column of sales. Let's see in Power VI desktop. Now I have Power V desktop open. Now I'm going to calculate a profit percentage. Right-click and create a new measure. And my measure is profit percentage. Now you know already that for profit percentage we need few components already gets calculated. Meaning either I have profit and COGS or sales amount or either I have COGS sales amounts then cross mark gross margin then we'll have the formula at gross margin divide by total COGS. So in this case I'm going to have first The Profit. So defining a variable where meaning you are defining a variable it's not necessary to write in lower cases you can write in upper cases also. I'm going to have 4et sum of profit. This is my profit but I going to calculate profit percentage so need another value which is COGS. Then define again COGS and how going to find COGS ERS I don't have COGS here do I have sales.

amount can I go for checking click this table icon here. You can check what are the attributes you have. I have here in the right side we have sales amount and the profit amount. If I'm going to profit minus to the sales, then we have C GS. So I am going to follow the same rule here. I have to find cogs meaning. So sum of sales amount minus sum of profit amount, this will give me a cogs. Now in this way, I have defined two variables. First is my profit, which results using this formula. Second cogs, which results by this formula. Then I have another formula to use profit by C GS. You're going to use again a new variable profit percentage, and this will come by using formula divided by profit divided by CS. So WR here profit, and now this profit variable is available as X as a prefix. So select this x profit because this X indicates that you have a calculation or Define a variable. So just select this one. Now select C GS. Now I have three variables in order to have profit percentage.

have the final result using this formula. But how can I show if I enter this? It will show you an error. So what will you do? There is a rule. See, I press enter. This giving error because whenever you use a variable, then you have to use return keyword. So write return. What formula you want to be return? This formula, because this formula will calculate profit divided by CS. This is what I expect as a profit percentage. So return this name. So write this profit percentage. Now hit enter. Now there is no error. I can format this by percentage because profit percentages are I have calculated. Now drag a card where I can show you what is my profit percentage. Here I have my profit percentage. I'm going to calculate the same profit percentage in either way. So just right click new measure profit percentage. I'm not going to use a variable in this time. Then you will see what the difference between a regular formula or a formula using variable. I'm going to divide first the sum of profit, comma. How we'll get the cogs? Then I have used sum of sales.

amount minus sum of profit amount. Now, this is my formula. This is my formula and I have used sum of sales amount and sum of profit amount. This makes our COGS. Then this will be the denominator and this will be your numerator. Then you will have the divide function. Then you will get a profit percentage. Now hit enter. Now change the format. Now draw a card so that you will have your profit percentage here. Now you can see you have the same result using two types of calculation. One calculation comes with what we have been doing as a regular formula and calculation comes up using variable. You can see when I use a regular formula, you can see just like a very a bunch of code. A layman or the beginner can't understand what is happening here. But in case of using variable, it is clearly visible that I'm going to create calculate the profit here. I'm going to calculate a COGS. Then I'm going to calculate a profit percentage. Then I want to return only this profit percentage. In this way, I get.

18.42. Which one you like? Just comment it. Variable makes your complex formula easy so that anyone can understand. Variables are very helpful when you are going to use a complex formula. But variable will broken down your complex formula a single formula into parts so that small small parts can make a complex formula. And those small parts is very understandable way so that anyone can understand. So variables are useful. Try to use variables every time. Thanks for watching.

power bi variables | variables in power bi | power bi | power bi tutorial for beginners'

Akhilesh Gupta

Variables in Power BI allow users to store and manipulate data values within memory.

They can be used to define names for specific values and perform calculations, such as calculating profit percentage.

Using variables, users can store and work with components like profit, cost of goods sold, and sales amounts in a more efficient way within Power BI Desktop.

Calculating Profit Percentage in Power BI Desktop

To calculate the profit percentage, you need to define three variables: the sum of profit, the sum of sales amount, and the sum of COGS.

The profit percentage is then calculated by dividing the profit by the COGS, using the variables defined.

Using Variables in Formulas for Calculating Profit Percentage

To avoid errors, the formula for calculating profit percentage should be written with the "return" keyword when using a variable.

The use of a variable in the formula provides a clear and understandable calculation process for determining the profit percentage, as opposed to using a regular formula, which can appear more complex and be harder to interpret.

This approach simplifies the process and makes it more accessible, especially for beginners.

The Benefits of Using Variables in Complex Formulas

Variables make complex formulas easier to understand and use.

By breaking down the formula into smaller, understandable parts, variables make the overall formula more accessible.

Using variables is highly useful and recommended for anyone dealing with complex formulas.