Curso Básico Power BI 2025 - Aula 4 - Introdução às Fórmulas do Power BI

Introduction to Power BI Formulas

Importance of Learning Formulas

• The session emphasizes the significance of learning to create formulas in Power BI for effective data analysis and indicator generation.

• Viewers are encouraged to like the video, which helps increase its visibility on YouTube.

Preparing for Analysis

• The next step involves determining the analyses and indicators needed based on existing data relationships.

• Examples of desired analyses include total products sold, return quantities (both absolute numbers and percentages), total revenue, and average ticket size.

Creating Calculations in Power BI

Specific Sales Analysis

• The instructor plans to analyze sales specifically from physical stores versus online sales.

• This differentiation allows for targeted insights into sales performance across different channels.

Steps to Create a Formula

• To calculate total products sold, one must access the relevant table containing sales data.

• A new measure is created by right-clicking within the table and selecting "New Measure" to define a formula that sums up sold quantities.

Using Functions in Power BI

Naming Measures and Using Functions

• When creating a measure, it’s essential to name it appropriately; for example, "total vendas" (total sales).

• The formula uses English functions such as SUM for summation, with other functions like AVERAGE, MIN, and MAX also available.

Column Reference in Formulas

• Users need to specify the column name containing values they wish to sum; here it's referred to as "qtd vendida" (quantity sold).

Viewing Results in Power BI

Understanding Measure Outputs

• Newly created measures do not display results directly in tables but can be visualized through reports or charts.

Creating Visual Representations

• To view results from calculations, users can create visuals such as cards that display key metrics like total sales figures.

Understanding Power BI Formulas and Measures

Creating Basic Measures

• The speaker introduces the concept of creating a measure in Power BI, emphasizing that for simple calculations like sum or average, it is not mandatory to create a formula if a column with those values already exists.

Using Columns Directly

• Instead of using a measure for total sales, the speaker demonstrates how to create a card by directly pulling data from the "quantity sold" column, achieving the same result without additional formulas.

Advantages of Creating Formulas

• The necessity of creating formulas is discussed; they allow for reuse in future calculations. This is particularly important as more complex calculations cannot be performed simply by dragging columns into visuals.

Limitations of Simple Calculations

• The speaker notes that direct column usage only works for basic calculations (e.g., sum, average). For more complex analyses, users must learn to use formulas effectively.

Example: Total Returns Calculation

• A new measure called "Total Returns" is created using the sum of the "quantity returned" column. This illustrates how measures can provide insights beyond simple sums.

Calculating Return Percentages

• To find the percentage of returns relative to total sales, both total returns and total sales measures are necessary. This highlights why having these formulas prepared in advance is beneficial.

Simplifying Complex Formulas

• The speaker explains how to calculate return percentages by dividing total returns by total sales. Utilizing pre-existing measures simplifies this process significantly compared to recreating lengthy formulas each time.

Formatting Results for Clarity

• After calculating the return percentage, it's noted that initial results may appear rounded. Adjusting decimal places allows for clearer visibility of actual values in reports.

Understanding Sales and Returns Data in Power BI

Formatting Percentages

• The speaker demonstrates how to format a number as a percentage in Power BI, showing that the return percentage is 0.36, equivalent to 0.0036 when not formatted.

• Emphasizes the importance of having sales and returns metrics readily available for analysis.

Calculating Total Revenue

• Discusses the lack of a direct column indicating revenue from each sale, which complicates total revenue calculations.

• Mentions that while unit price information exists in the product table, it needs adjustment (dividing by 100) due to formatting issues.

Adjusting Data in Power Query

• The speaker notes an oversight regarding necessary adjustments in both product and sales tables within Power BI.

• Explains how to access Power Query to make data adjustments and resolve file location errors encountered during data import.

Resolving File Location Errors

• Describes steps to help Power BI locate missing files by adjusting data source settings.

• Provides guidance on navigating through directories on Google Drive to find the correct file for base sales data.

Finalizing Data Adjustments

• Reiterates the need for consistent functioning of Power Query across all databases for effective alterations.

• Demonstrates how to adjust unit prices by dividing them by 100 within Power Query before calculating total revenue.

Creating New Columns for Revenue Calculation

• Introduces functionality in Power Query that allows users to create new columns based on calculations, specifically multiplying sold quantities by unit prices.

• Outlines the process of selecting relevant columns and using them to generate a new column representing total revenue from sales.

Calculating Sales Revenue in Power BI

Performing Calculations on Columns

• The speaker demonstrates how to select two columns in Power BI and perform a multiplication operation to calculate sales revenue.

• Example calculations are provided, showing the total revenue generated from selling different quantities of products at specified prices.

Finalizing Data Modifications

• After modifying column names (renaming to "Faturamento na Venda"), the speaker applies changes to update the data within Power BI.

• The updated table now reflects accurate sales figures, allowing for further analysis of total revenue.

Creating Total Revenue Measure

• A new measure called "Faturamento Total" is created by summing up the values in the "Faturamento na Venda" column.

• The total revenue is displayed as $548 million, with a note that all sales figures are in dollars.

Formatting Currency Values

• The speaker discusses formatting options for displaying monetary values, specifically adding currency symbols and adjusting decimal places.

• The correct currency format (USD) is selected for clarity in financial reporting.

Calculating Average Ticket Price

• An average ticket price ("Ticket Médio") measure is introduced, calculated using the average function on the "Faturamento na Venda" column.

• Instructions are given on how to create this measure efficiently using shortcuts within Power BI.

Analyzing Physical Store Sales

• The discussion shifts towards analyzing sales specifically from physical stores versus online stores.

• The relationship between sales data and store type information is highlighted as crucial for accurate calculations.

This structured approach provides a clear overview of key concepts discussed in the transcript while linking directly to specific moments for easy reference.

Creating Conditional Calculations in Power BI

Introduction to Conditional Formulas

• The speaker begins by explaining how to create a new measure using the right-click function, aiming to calculate sales specifically for physical stores.

• The key formula introduced is CALCULATE, which allows calculations based on specific conditions, such as summing sales from physical stores only.

Using the CALCULATE Function

• The speaker elaborates on the versatility of the CALCULATE function, indicating it can be used for various metrics like average revenue or minimum sales under certain filters.

• To use CALCULATE, one must first define the expression (the calculation), followed by specifying filters or conditions.

Step-by-Step Calculation Example

• A new measure named "vendas online" (online sales) is created. The initial step involves summing sold quantities while applying a filter for online stores.

• The filter condition is set by selecting the "tipo de loja" (type of store) column and specifying that it should equal "online".

Result Analysis

• After applying the CALCULATE function, the total online sales are calculated, demonstrating how filtering impacts results compared to overall sales figures.

• The speaker clears unnecessary filters and prepares to display results in a report format.

Visualizing Sales Data

• A new card visualization is created to show total online sales versus total sales across all stores, highlighting significant differences in figures.

• A new measure for calculating the percentage of online sales relative to total sales is introduced, resulting in an approximate 10% share of online transactions.

Advanced Data Analysis Techniques

• The discussion transitions into creating more complex visualizations and analyses within Power BI, including monthly totals and returns data.

• An example matrix visualization is constructed to break down revenue by product brand or category, showcasing flexibility in data representation.

Customizing Data Views

• In this section, different dimensions such as brands or categories are explored within matrices to analyze revenue distribution effectively.

• The ability to pivot data views based on various criteria like continent or country emphasizes Power BI's robust analytical capabilities.

Power BI: Analyzing Return Rates Across Countries

Understanding Return Percentages

• The speaker discusses the calculation of annual return percentages for different countries, emphasizing the importance of understanding these metrics.

• By extracting total revenue and return percentage data from sales tables, users can analyze how returns vary by country.

• This analysis will be instrumental in creating visual representations (graphs) that depict return rates across regions, clients, and products.

Dashboard Creation Insights

• The speaker highlights the value of having comprehensive metrics to facilitate various analyses and dashboard creation.

• In the next lesson, there will be a focus on how dashboards function within Power BI, aiming to enhance visual comprehension of data.

Engagement Encouragement

• Viewers are encouraged to like the video if they found it helpful and subscribe to stay updated with future content related to Power BI and Excel.