Concept Breakdown
Calculated columns are an integral part of data modeling in Power BI. Unlike measures, which are calculated at query time and return a scalar value, calculated columns are added to the table itself and their values are computed during data refresh. They consume memory and can be used in rows, columns, filters, or values in visuals, similar to regular columns. The DIVIDE function is a robust DAX function used for division, providing built-in handling for division by zero errors, returning an alternate result (defaulting to BLANK) instead of an infinity symbol or an error.
What You Will Learn
Understand when to use Calculated Columns, DIVIDE in a Power BI model.
Practice the concept inside a real PBIX report rather than only reading syntax.
Complete a intermediate-level task and verify the result with a hidden answer check.
Practice in Power BI
The starter PBIX link has not been attached yet. The student workflow is ready for the admin to connect the file.
Download and unzip the provided 'Music Tours - Calculated Columns.pbix' file.
Open the .pbix file in Power BI Desktop.
Go to the 'Table view' and select the 'Tours' table.
Add a calculated column named 'Tour length' that calculates the length of each tour in years by subtracting 'Start year' from 'End year' and adding 1.
Create a table visual showing a list of tours, sorted by 'Tour length' in descending order (longest tour at the top).
Add a chart visual showing the sum of 'Tour length' for each 'Artist', sorted with the longest touring artist at the top.
Add three more calculated columns:
- 'Shows per year': Divide 'Shows' by 'Tour length'.
- 'Avg show revenue': Divide 'Actual gross' by 'Shows'.
- 'Avg attendance': Divide 'Attendance' by 'Shows'.
Apply appropriate formatting to these new columns for readability (e.g., number, currency).
Add a final calculated column named 'Avg ticket price' which divides 'Actual gross' by 'Attendance', using the DIVIDE function to handle potential blank 'Attendance' figures and avoid infinity symbols.
Save and close the report.
Starter DAX
New Column = [Column1] + [Column2]Expected Outcome
A 'Tours' table with several new calculated columns: 'Tour length', 'Shows per year', 'Avg show revenue', 'Avg attendance', and 'Avg ticket price'. The table visual will display tours sorted by length, and the chart visual will show artists by total tour length. All calculations, especially divisions, will handle potential errors gracefully, showing meaningful results or blanks instead of errors.