Converting Excel Reports to Power BI: Examples of excel formulas and PowerBI (DAX) equivalents with examples
In the previous articles, we discussed a High Level Approach to Converting an Excel Spreadsheet to a PowerBI report , Issues You Might Face during this process, Common Power BI visuals and their Excel equivalents.
Certainly! Here are some common Excel formulas and their equivalent DAX (Data Analysis Expressions) functions in Power BI, along with examples:
Excel Formula: SUM
=SUM(A1:A5)DAX Equivalent: SUM
Total Sales = SUM('Sales'[Amount])In this example, the Excel SUM formula adds up values in cells A1 to A5, while the DAX SUM function calculates the total sales amount from the 'Sales' table in Power BI.
Excel Formula: AVERAGE
=AVERAGE(B1:B10)DAX Equivalent: AVERAGE
Average Price = AVERAGE('Products'[Price])The Excel AVERAGE formula calculates the average of values in cells B1 to B10, while the DAX AVERAGE function computes the average price from the 'Products' table in Power BI.
Excel Formula: COUNTIF
=COUNTIF(C1:C20, ">10")DAX Equivalent: COUNTROWS with FILTER
High Sales Count = COUNTROWS(FILTER('Sales', 'Sales'[Amount] > 10))Excel's COUNTIF formula counts the number of cells in the range C1 to C20 that are greater than 10, while the DAX COUNTROWS function combined with FILTER counts the rows in the 'Sales' table where the amount is greater than 10.
Excel Formula: VLOOKUP
=VLOOKUP(D2, 'Product List'!A2:B100, 2, FALSE)DAX Equivalent: RELATED
Product Name = RELATED('Product List'[Product Name])In Excel, VLOOKUP searches for a value in the 'Product List' range and returns the corresponding value from the second column. In Power BI, the RELATED function retrieves related values based on relationships defined in the data model.
Excel Formula: IF
=IF(E2 > 100, "High", "Low")DAX Equivalent: IF
Sales Category = IF('Sales'[Amount] > 100, "High", "Low")Both Excel and Power BI use the IF function to perform conditional calculations. In this example, if the sales amount in Power BI's 'Sales' table is greater than 100, it's categorized as "High," otherwise "Low."
Excel Formula: CONCATENATE
=CONCATENATE(A1, " - ", B1)DAX Equivalent: CONCATENATEX (or CONCATENATE in Power BI 2022 and later)
Product Details = CONCATENATEX('Products', 'Products'[Name] & " - " & 'Products'[Category], ", ")Excel's CONCATENATE function combines text values in cells A1 and B1 with a separator. In Power BI, you can use the CONCATENATEX function (or the CONCATENATE function in Power BI 2022 and later) to concatenate text values from columns in a table.
These examples illustrate how you can translate common Excel formulas into DAX expressions in Power BI to perform similar calculations and transformations within your Power BI reports and dashboards.
Need help with Microsoft 365?
We've got you covered! Whether you're facing technical glitches or need expert guidance, our team of Microsoft 365 experts is here to assist you. Don't hesitate to reach out for support. Contact us now, and let's conquer your challenges together! Your smooth and efficient Microsoft 365 experience awaits!