Interpret big data sources and summaries

You will use Power BI Desktop to carry out the demonstration tasks in this module. This tool would enable you to learn the hands-on skills required for this module.

Installing Power BI Desktop

Do the following to prepare your computer with the required software before proceeding any further in the module content.

• Install Power BI Desktop software.
• Refer to the article Introduction to Power BI | Microsoft Learn

The following video will demonstrate the installation steps in detail.

Accessing big data sources and summaries

Power BI Desktop has three views: Report, Data and Model. To access data summaries and data sources as required for this unit, you will mainly access the ‘Report view’ and ‘Data view’.

• The Power BI 'Report' view  - can be used to view big data summaries.
• The Power BI 'Data' view - can be used to access data source details such as tables, measures and other data that is associated with the Power BI reports.

The following short video will demonstrate how you can navigate from one view to another in Power BI desktop.

Microsoft Power BI is used with permission from Microsoft. Power BI is a trademark of the Microsoft group of companies.

Refer to the article What is Power BI Desktop? from Microsoft Learn for more information on the three views.

Making data fields accessible in the report view

Sometimes data columns in the ‘Data’ view may be hidden from the ‘Report’ view. To ensure the required fields from the ‘Data’ view are visible and accessible from the report view, you will need to unhide the required tables/columns either from the ‘Data view’ or the ‘Model’ view.

The following video demonstrates how hidden data tables and fields can be made visible and accessible in the ‘Report’ view of a Power BI Desktop file.

Microsoft Power BI is used with permission from Microsoft. Power BI is a trademark of the Microsoft group of companies.

Practical activity 1: Use Power BI to access data sources and summaries

• Download the zipped file Demo 1 - Access data sources and summaries_v1.zip which contains the Power BI work file required for this activity.
• Unzip the downloaded file to find the Power BI work file inside of it.
  • Note: In a Windows computer, you can right-click the file and select 'Extract All...' to unzip the file.
• Open the Power BI work file 'Demo 1 – Access data sources and summaries.pbix' using the Power BI Desktop application and do the following tasks.

Note: If you are unsure of the steps required to perform any of the tasks in this activity, you can expand the ‘Hint’ section to see the detailed steps. A screenshot is provided at the end under ‘Checkpoint’ so that you can check your work against the desired end result.

Task 1 - Access big data summaries

1. Navigate to the ‘Report’ view.
2. View the XYZ Manufacturing Financials Overview report page.
3. Customise this page to only show data from the year 2021.

Task 2 - Access big data sources

Using the same Power BI work file, do the following:

1. Navigate to the ‘Data’ view.

2. View the data source tables and columns available in the Power BI work file.

3. Make the following fields accessible in the ‘Report’ view of the file.

   1. 'Key Measures'[Gross Profit]
   2. ‘Key Measures’[Total Manufacturing Cost]
   3. ‘Key Measures’[Total Sales]
   4. Date[Year]
   5. Date[Month]
   6. Financials[Product ID]
   7. Financials[Profit]

Checkpoint

Once all tasks are completed correctly, you should get the following result, as displayed in the screenshot.

Applying descriptive statistics

Descriptive statistics are explanations and information components that can be used to describe data sets to ensure that they are able to be understood. Descriptive statistics can be applied to support operational decision-making by using the information as evidence that can be used to:

• form a position
• select an option
• determine an answer to a question.

What is univariate analysis?

Earlier in this module, you learnt basic statistical concepts that help to describe the central tendency (mean, median, mode), dispersion (range, variance, standard deviation) and shape (skewness, kurtosis) of a single numerical variable. Keeping these basic concepts in mind, analysts must perform univariate analysis of the key numerical variables used in an analysis.

Some important facts to remember when evaluating statistics are as follows. ¹⁷

• Examine both the mean and the median and identify if they are similar or whether they are very different. 
• If only the mean is presented, then one cannot determine whether the data are skewed or symmetrical or whether the median might be a better measure of central tendency than the mean. 
• In addition, one should look to see whether the standard deviation or interquartile range of a very skewed set of data has been included in the statistics provided. Without these, one cannot determine the amount of variation that exists in the data.

The following video will take you through the process of analysing the statistical measures of numerical variables. Pay close attention to how the descriptive statistics obtained for each numerical variable tell a story about its central tendency, dispersion and shape.

Note: This demonstration uses Microsoft Excel to perform basic demonstrations. The same concepts will later be used to carry out demonstration tasks in Power BI Desktop.

Practical activity 2: Creating statistical measures in Power BI Desktop

The tasks in this activity will help you practice what you learnt so far in this topic and help you prepare for the demonstration tasks in your formal assessment. You will specifically learn how to:

• create a report page using a given template and customise report elements
• create statistical measures required for the univariate analysis
• record numerical data in the report.

Use the same Power BI work from practical activity 1, 'Demo 1 - Access data sources and summaries' (where you accessed the financials dataset) and save it as ‘Demo2-Univariate analysis’. In this activity, we will conduct a univariate analysis of the Financials[Profit] data.

Refer to the Statistical functions (DAX) - DAX for more information on the statistical functions used in this exercise.

Note: Additional steps on how to perform the tasks are provided in the ‘Hint’ sections. Screenshots are provided within the 'Hint' sections and under the 'Checkpoint' sections so that you can check your work against the desired end result.

Task 1 - Customise a new report page for the univariate analysis

In the Power BI Desktop work file, duplicate the ‘XYZ Manufacturing Report template’ and rename it as ‘Profit: Univariate Analysis Report’.

Step 1 – Create a new report page using XYZ Manufacturing’s standard report template.

Hint:

To do this, right-click on the ‘XYZ Manufacturing Report template’ > Select ‘Duplicate Page’. 

The following screenshot further demonstrates how this task can be done.

Step 2 – Rename the new report page as ‘Profit: Univariate Analysis Report’.

Hint: 

Double-click on the duplicate report page name (at the bottom) and start typing in the new name. Once done, press the ‘Enter’ key.

The following screenshot further demonstrates how this task can be done.

Step 3 – Customise the ‘Title’ of the report page as ‘PROFIT – UNIVARIATE ANALYSIS’.

Hint: 

• First, select the report element that contains the title (e.g. the black rectangle at the top)
• Then, expand the ‘Format’ pane (at the right side of the Power BI Desktop application) and do the following.
  • Under the ‘Shape’ tab, expand the ‘Style’ options and scroll down to the ‘Text’ section.
  • Type in the new title name in the input text box.

The following screenshot further demonstrates how this task can be done.

Step 4 – Customise the ‘Sub-title’ of the report page to state the goal of the analysis as “GOAL: TO ANALYSE THE DISTRIBUTION OF THE ‘PROFIT’ FIELD”.

Hint: 

Follow the same process in Step 3.

The following screenshot further demonstrates how this task can be done.

Task 2 - Create the statistical measures required for the univariate analysis

Refer to the Tutorial: Create your own measures in Power BI Desktop - Power BI | Microsoft Learn  for more step-by-step instructions on creating measures in Power BI Desktop.

Do the following tasks in the ‘Data’ view of the Power BI work file.

Step 1: Create a new empty table called ‘Statistics’

Within the ‘Statistics’ table, do tasks 2 to 11.

Step 2: Create a new measure to calculate the arithmetic mean of the ‘Profit’ field called ‘Profit Average’.

Step 3: Create a new measure to calculate the median of the ‘Profit’ field called ‘Profit Median’.

Step 4: Create a new measure to calculate the mode of the ‘Profit’ field called ‘Profit Mode’ using the following sample DAX formula.

Note: Ensure that you replace the TableName[X] with the fully qualified column name of the 'Profit' field in the DAX statement provided.

= MINX( 
     TOPN (1, 
          ADDCOLUMNS( 
               VALUES(TableName[X]),"Frequency", CALCULATE( COUNT (TableName[X]))
                    ), 
                    [Frequency], 0 
          ), 
          TableName[X] 
      )

Hint:

• The following screenshot further demonstrates how the standard protocols of writing DAX statements are used to create this measure.
• Notice that several DAX functions (e.g. MINX, TOPN, ADDCOLUMNS, CALCULATE, COUNT and VALUES) are used to calculate the mode.

Step 5: Create a new measure to calculate the standard deviation of the ‘Profit’ field called ‘Profit Standard Deviation’.

Step 6: Create a new measure to obtain the minimum value of the ‘Profit’ field called ‘Profit Min’.

Step 7: Create a new measure to obtain the maximum value of the ‘Profit’ field called ‘Profit Max’.

Step 8: Create a new measure to calculate the range of the ‘Profit’ field called ‘Profit Range’.

Step 9: Create a new measure to calculate the first quartile of the ‘Profit’ field called ‘Profit Q1’.

Step 10: Create a new measure to calculate the third quartile of the ‘Profit’ field called ‘Profit Q3’.

Step 11: Create a new measure to calculate the interquartile range (IQR) of the ‘Profit’ field called ‘Profit IQR’.

Task 3 - Record numerical data in the report using visuals

Step 1: Add a ‘Multi-row Card’ visual to the report page to visualise the ten (10) statistical measures created previously in task 2. 

Place this visual on the top row within the body of the report.

Note: The report has placeholders (rectangular shape elements) that indicate the placement of visuals and their approximate size. You may wish to delete these placeholder elements from the report page or place the visuals on top of these elements.

Step 2: Add measures to the visual so that the required numerical information can be displayed in the report.

Step 3: Customise the 'Multi-row card' visual according to the following specifications.

Checkpoint

Once these steps are done correctly, you should get the following result, as shown in the screenshot.

Visualising distributions using frequency tables and histograms

To understand the basics of what a histogram is, what it consists of and why it is used to visualise statistical measures, watch the following video demonstration. Pay close attention to the concepts of ‘frequency counts’ and ‘bins’ and how they are used to represent and group numerical data.

Note: This demonstration uses Microsoft Excel to perform basic demonstrations. The same concepts will later be used to carry out demonstration tasks in Power BI Desktop.

Binning in Power BI Desktop

Bin sizes can be set for numerical fields in Power BI Desktop. Bins can be created for calculated columns, but not for measures. It is important to allocate the right bin size in order to visualise the data properly. To learn more refer to the article Use grouping and binning in Power BI Desktop from Microsoft Learn. Watch the following video that demonstrates how to create groupings and bins in Microsoft Power BI Desktop.

Creating a histogram and adding a distribution curve in Power BI

The following video will demonstrate how to create a histogram and how a distribution curve can be added. Pay close attention to how the NORM.DIST function is used in the demonstration.

Practical activity 3: Visualise the distribution of data

The tasks in this activity will help you practice what you learnt and will help you prepare for the demonstration tasks in your formal assessment. You will specifically learn how to create a frequency distribution table and a histogram to visualise the distribution of the data. Continue to work on the same Power BI work file from practical activity 2, ‘Demo2-Univariate analysis’.

Task 1: Create a frequency distribution table

Step 1: Within the ‘Financials’ table, create bins to group the data in the 'Profit' field using an appropriate bin size.

Step 2: Within the 'Statistics' table, create a new measure to calculate the count of the ‘Profit’ field called ‘Profit Frequency'.

Step 3: Add a 'Table' to the report to create a frequency distribution table.

Place this visual at the bottom-right corner body of the report and resize according to the placement guidelines.

Note: The report has placeholders (rectangular shape elements) that indicate the placement of visuals and their approximate size. You may wish to delete these placeholder elements from the report page or place the visuals on top of these elements.

Step 4: Add the ‘Profit(bins)’ and ‘Profit Frequency’ measures to the visual to display as columns on the table.

Step 5: Customise the 'Table' according to the following specifications.

Checkpoint

Once these steps are done correctly, you should get the following result, as shown in the screenshot.

Task 2: Create a histogram

Step 1: Within the ‘Financials’ table, create a new calculated column called ‘Profit Distribution Curve’ using the NORM.DIST function for the ‘Profit’ field.

Refer to the Tutorial: Create calculated columns in Power BI Desktop - Power BI | Microsoft Learn for more information on how to create calculated columns.

Note: When using the NORM.DIST calculation formula, you will need to type in the exact values obtained for the associated arithmetic mean and standard deviation values for the ‘Profit’ field as displayed in the ‘Multi-row card’ visual in the report.

For more information on how to use this function, refer to NORM.S.DIST function (DAX).

Step 2: Add a ‘Line and clustered column chart’ visual to display the distribution of the ‘Profit’ field according to the following specifications.

Step 3: Customise the title of this visual to '‘Distribution of Profit (Histogram)’ according to the standard guidelines as applied to other visuals in the report:

Checkpoint

Once these steps are done correctly, you should get the following result, as shown in the screenshot.

Applying insight analysis

Insight analysis refers to the analysis of the big data insights that have been collected and that will be applied to support operational decision-making. The insights from the big data sets should be used to assess and inform on problems that exist so that operational decision-making can be maintained.

When insights are collected from the big data sets, these will need to be analysed using appropriate tools so that they can be better understood.

Once insights are understood, these will need to be used to justify and substantiate decision that applies within the organisation.

Using smart narratives to record insights into the analysis

Smart narratives are used to provide brief explanations of the overall results and key outcomes of a Power BI report. They are a type of visual that can be added to a report in the form of text to outline summaries of trends, key outcomes, explanations and context.
To learn how to create smart narrative summaries in Power BI, refer to Smart narratives tutorial - Power BI.

The following video further demonstrates how to create smart narratives and customise them as required using dynamic values.

Practical activity 4: Displaying statistical analysis insights

The tasks in this activity will help you practice what you learnt so far in this topic and will help you prepare for the demonstration tasks in your formal assessment. You will specifically learn how to generate a report of the key outcomes of the analysis using the smart narratives feature in Power BI Desktop.

Continue to work on the same Power BI work file from practical activity 3, ‘Demo2-Univariate analysis’. 

Task 1 - Create required statistical analysis measures

Create a new measure to calculate ‘Pearson’s Median Skewness’ for the ‘Profit’ field called ‘Profit Median Skewness’.

Note: Use “Pearson’s Median Skewness” equation to calculate the skewness.

$$\text{Pearson's Median Skewness} = \frac{\text{3(Mean-Median)}}{\text{Standard Deviation}}$$

Task 2 - Record numerical data in the report

Within the ‘Statistics’ table, create a new calculated measure to determine the shape of the ‘Profit’ data called ‘Shape of the Profit data’ using the following DAX formula.

Note: You must customise the DAX formula by replacing the 'X' with the variable name 'Profit'.

Shape of the (X variable) data =
VAR SK = [X Median Skewness] // Name of the measure that calculates the skewness of the variable
VAR _1 = SK > 0
VAR _2 = SK < 0
RETURN (
IF (_1, "Positively Skewed",
IF (_2, "Negatively Skewed", "No Skew; Data is normally distributed")) )

Shape of the Profit data =
VAR SK = [Profit Median Skewness]
VAR _1 = SK > 0
VAR _2 = SK < 0
RETURN (
IF (_1, "Positively Skewed",
IF (_2, "Negatively Skewed", "No Skew; Data is normally distributed") ) )

Task 3 - Generate a report of the key outcomes of the analysis

Step 1: Add a 'Smart narrative' visual to the report. 

Place this at the top-right corner within the body of the report.

Step 2: Customise the title of this visual to ‘Statistical Analysis of Profit’  and apply standard formatting.

Also, set the background colour of the visual to '#E8EBE7'.

Step 3: Customise the summary text ‘Profit’ data to only include information related to the statistical analysis, such as the following.

• Shape of the data: Indicate whether it is symmetrical, positively skewed or negatively skewed. Add here a dynamic value to display the relevant information from the previously created measures ‘Shape of the Profit data’ as appropriate.
• Central tendency: Indicate where the data is centred in terms of mean, median and mode? Are there any observations that are radically different from the rest which may influence the arithmetic mean?
• Spread of the data: How broadly is the data dispersed? Range (Max value – Min value) and IQR. Are there huge differences between the range and the IQR, indicating that extreme values may be present in the data?

Customise the text in the smart narrative visual to be font type ‘Segoe UI’ and size 12.

CHECKPOINT

Once these steps are done correctly, you should get the following result, as shown in the screenshot.

Practical activity 5 - Putting it all together

Note: This activity will help you prepare for the demonstration tasks in your formal assessment. The analysis in this task will form the basis for the type of analysis we will discuss next in this topic.  Therefore, it is recommended that you attempt this activity in preparation for the next section.

Continue to work on the same Power BI work file from the previous practical activity, and rename the file as ‘Demo3 -Cost Univariate Analysis’.

Tasks: Conduct a univariate analysis for the ‘Manufacturing Cost’ field of the ‘Financials’ dataset by:
a.    making the Financials[Manufacturing Cost] field accessible from the report view of Power BI
b.    creating the necessary statistical measures for the analysis
c.    recording numerical data in the report
d.    creating a frequency table and histogram to visualise the distribution of the data
e.    generating a report of the key outcomes of the analysis.

CHECKPOINT

Once these steps are done correctly, you should get the following result as shown in the screenshot.

Findings from the statistical analysis refer to the information components that have been discovered, such as:

• outcomes
• trends
• patterns
• links
• relationships
• existence of extreme values in the data (outliers).

These findings will then need to be used to identify the insights that relate to the identified operational decision-making requirements. For example, let us explore how outliers or extreme values, when found in a dataset, could impact operational decision-making.

The effects of extreme values on operational decision making

Finding outliers in a dataset means that the result from any further analysis using this data may be impacted and may even provide inaccurate outcomes or skewed results.
To ensure that the operational decision-making does not have any effects from extreme values, it is important to implement a method to isolate the outliers from the rest of the data in the analysis.

When outliers (or extreme values) are present in the key numerical variables analysed, it will influence the mean value of those variables. Outliers will pull the value of the mean toward the extreme values. Therefore, identifying outliers is important as some analysis methods are sensitive to outliers and produce very different results when outliers are included in the analysis. ¹⁷

The following video discusses the effects of outliers on the distribution of data.

When analysts need to decide to remove or keep outliers, the following flow chart can help to make a decision. ³²

Finding outliers and isolating incorrect results

Outliers can be found for numerical variables that do not have a defined range of possible values. Although there is no standard for defining outliers, statistical measures such as standard deviation or the interquartile range can be used to find outliers in numerical variables.

When performing the practical activity of conducting a univariate analysis, you learnt that measures such as ‘standard deviation’ and ‘interquartile range’ can be easily computed using analytical tools. Therefore, spotting outliers can also be done using analytical tools.

Using Z-scores to find outliers

When extreme data points in a numerical variable are converted to standardised values (Z-scores), this will help identify how many standard deviations away they are from the mean. Generally, values with a z score greater than three (3) or less than minus three (-3) are often determined to be outliers. ³³

A calculated column can be created to compute the z-score values in Power BI Desktop using the following standard formula.

$$\textit{Z-Score} = \frac{(X_i-\mu )}{ \sigma}$$

Isolating outliers from visuals in the analysis

In situations where outliers are genuinely present in numerical data (not due to errors in data entry), organisations would want to visualise their analytical results with/without outliers. Therefore, analysts need to know how to implement this.

Analysts can implement the following general steps to isolate outliers from an analysis using their preferred analytical tool.

• Step 1 – Create a calculated column to compute the standardised (z-score) values. Note: The formula will need to use previously calculated average and standard deviation figures of the data.

For example, a conditional column using an if condition to tag the values in each row of data in the previously calculated ‘Z-Score Cost’ column will look like the following:

Z-Score Cost = DIVIDE(([Cost]- X), Y )                    

• Step 2 – Create a conditional column to categorise the z-score values as ‘Outliers’ and ‘Non-outliers’. For example, a conditional column using if condition to tag the values in each row of data in the previously calculated ‘Z-Score Cost’ column will look like the following:

Cost Outliers/Non-outliers = If (
                              ( [Z-Score Cost] > 3 || [Z-Score Cost] < -3 ),
                              "Outliers", "Non-outliers"
                              )

• Step 3 – Filter visuals in the analysis to show results with or without outliers.

For example, this can be done by using a slicer visual in the report, where the option is provided to the user to select only outliers, only non-outliers or include both in the analysis.

Practical activity 6: Isolating outliers from the analysis

Let’s put into practice what you’ve learnt so far in this topic. This activity will help you prepare for the demonstration tasks in your formal assessment. You will specifically learn how to:

• create a calculated column to compute the z-score values
• create a conditional column to categorise outlier and non-outlier values
• filter the outliers from the visuals in a report.

Note: If you are unsure of the steps required to perform any of the tasks in this activity, you can expand the ‘Hint’ sections to see the detailed steps (Note: If similar tasks were performed in previous practical activities, these tasks would not include hints.). Screenshots are provided under the ‘Checkpoint’ sections so that you can check your work against the desired end result.

Use the same Power BI work file you created in the last practical activity (where you have performed the univariate analysis) and save it as ‘Demo4-Isolating outliers’.

Task 1 - Create a calculated column to record z-score values

Create a calculated column called ‘Profit Z-scores’ in the ‘Financials’ table that calculates standardised values based on the formula:

$$\textit{Profit Z-Scores} = \frac{([Profit]-\ Profit \ Average )}{ \ Profit \ Standard \ Deviation}$$

To do this, you must go to the ‘Data’ view in Power BI Desktop. You must enter the actual values for average and standard deviation as calculated in the previous univariate analysis.

CHECKPOINT

The following screenshot demonstrates how the standard protocols of writing DAX statements are used to create this calculated column.

Task 2 - Create a conditional column to categorise outlier and non-outlier values

Add a conditional column called ‘Profit Outliers/Non-outliers’ to isolate the outliers in the ‘Profit Z-scores’ column.

Note: If the ‘Profit Z-scores’ values are greater than three (3) and less than minus three (-3), the output should be ‘YES’ to indicate these values are outliers. Otherwise, the output should be ‘NO’ to indicate those values are non-outliers.

CHECKPOINT

The following screenshot demonstrates how the standard protocols of writing DAX statements are used to create this conditional column.

Task 3 - Filter the outliers from the visuals in the dashboard

Step 1: Add a new ‘Slicer’ visual to the ‘XYZ Manufacturing Financials Overview’ page.

Step 2: Position the slicer visual within the ‘Filters’ section on the left side of the report.

Step 3: Select the ‘Profit Outliers/Non-outliers’ column as the slicer input field.

Step 4: Customise the ‘Slicer’ visual title as ‘Display Profit:’ and apply the standard formatting style.

Step 5: Ensure the slicer shows the ‘Select all’ option. (hint: Visual > Slicer settings > Selection

Task 6: Filter the dashboard page to display the analysis without any outliers.

CHECKPOINT

Once these steps are done correctly, you should get the following result, as shown in the screenshot.

Practical activity 7: Try it yourself.

This activity will help you prepare for the demonstration tasks in your formal assessment. The analysis in this task will form the basis for the type of analysis we will discuss next in this topic. Therefore, it is recommended that you attempt this activity in preparation for the next section.

Continue to work on the same Power BI work file from the previous practical activity and save it as ‘Demo5 -Isolating Cost Outliers’.

Isolate the outliers of the ‘Manufacturing Cost’ field of the ‘Financials’ dataset by doing the following tasks.

• Task 1: Create a calculated column called ‘Cost Z-scores’ in the ‘Financials’ table that calculates standardised values for the values in the ‘Manufacturing Cost’ field.
• Task 2: Adding a conditional column called ‘Cost Outliers/Non-outliers’ to isolate the outliers in the ‘Cost Z-scores’ column.
• Task 3: Filter out the cost outliers from the visuals in the ‘XYZ Manufacturing Financials Overview’.

CHECKPOINT

Once these steps are done correctly, you should get the results as shown in the following screenshots.

Screenshots were taken using Power BI Desktop © Microsoft

Identifying insights

Identifying insights refers to gaining a deeper understanding of an organisation's dataset by analysing information on a particular problem or decision-making requirement. This level of understanding helps organisations make better decisions than by relying on gut instinct.

The term 'actionable data insights' refer to insights that are specific and relevant enough that they lead to actions that can increase efficiency, revenue and profits.
Data insights will provide information about what the data is saying or what insights into the factors related to the decision-making can be identified.

Relating insights to operational decision-making requirements

Once findings have been assessed and the insights have been identified, it is important to acknowledge the relevance of those findings to operational decision-making requirements.

The identified operational decision-making requirements refer to the items that are within the decision-making scope as part of the work task or project.

Using the Pareto (80/20 Rule) analysis to identify insights

What is the Pareto principle (or 80/20 rule)?

The generic situation in which 80% of some output comes from 20% of some input is referred to as the Pareto principle. Therefore, the Pareto principle is also commonly known as the “80-20 rule”. ¹⁹
When applying the Pareto principle to any characteristic of interest, analysts need to know how to sort the data and calculate the cumulative percentage. To learn more about the Pareto principle and how it works, refer to the article The 80-20 Rule (aka Pareto Principle): What it is, How it works. ²²

The following video explains in simple terms and examples how the Pareto analysis is used as a common method to solve problems and in support of making operational decisions in businesses. 

Creating a Pareto Chart in Power BI

Refer to the article What Is Pareto Analysis? How to Create a Pareto Chart and Example (investopedia.com) to learn more about the pareto analysis.

The following video will demonstrate how a Pareto Chart is created in Power BI Desktop using DAX formulas.

Testing the Pareto rule

The following video discusses how the Pareto Rule can be tested using a sample dataset. Pay close attention to the various DAX statements used and how they are formulated to produce the required results in Power BI visualisations.

Creating smart narrative summaries from visuals

In Power BI, smart narratives can be used to auto-generate summary text of the data displayed in report visuals. The following video demonstrates various methods that can be used to generate and further edit smart narrative summaries.

For more information, refer to the article Create smart narrative summaries by Microsoft Learn.

Practical activity 8: Creating a basic Pareto Analysis dashboard

The tasks in this activity will help you practice what you learnt so far about the Pareto principle and will help you prepare for the demonstration tasks in your formal assessment.

You will specifically learn how to:

• create a report page using a given templates and customise report elements
• include required filters in the analysis
• create a numeric range parameter
• create the required calculated measures for the analysis
• record numerical data in the dashboard
• visualise data using a Pareto chart, table and a scatter chart
• generate summaries to report on analysis outcomes
• use parameters in the dashboard to identify insights that relate to operational decision-making requirements.

Use the same Power BI work file you created in the last practical activity and save it as ‘Demo6-Pareto Analysis’.

Note: If you are unsure of the steps required to perform any of the tasks in this activity, you can expand the ‘Hint’ sections to see the detailed steps. If similar tasks were performed in previous practical activities, these tasks would not include hints. Screenshots are provided under the ‘Checkpoint’ sections so that you can check your work against the desired end result.

Let us use the Pareto rule to see whether the Top N percentage (approx. 20%) of products cause 80% of the total manufacturing costs.

Task 1 - Create a new report page for the Pareto Analysis

Step 1 – Create a new report page using XYZ Manufacturing’s standard dashboard template.

Step 2 – Rename the new dashboard page as ‘Top N Product Costs: Pareto Analysis’.

Step 3 – Customise the ‘Title’ of the report page as ‘TOP N Product Costs | PARETO ANALYSIS’

Step 4 – Customise the ‘Sub-title’ of the report page to state the goal of the analysis as “GOAL: TO IDENTIFY THE TOP N PRODUCTS THAT CAUSES 80% OF THE MANUFACTURING COSTS”.

Task 2 – Include the required filters for the analysis

From the previous ‘XYZ Manufacturing Financials Overview’ page, copy and paste the following filters to this dashboard page.

• ‘Select year: ‘ – containing values from the input field ‘Year’.
• 'Select Cost data: ‘ – containing values from the input field ‘Cost Outliers/Non-Outliers’.

Task 3 – Create a numeric range parameter

Procedure: From Power BI Desktop, navigate to the ‘Modelling’ tab> ‘New parameter’ > Select ‘Numeric range’ and customise according to the following specifications.

Field formatting and placement: Configure the TopN% field as a ‘Percentage’ format with one decimal point. The correct format should be visible in the ‘Slicer’ visual. Place this visual in the ‘Filters’ section of the dashboard.

Task 4 – Create the required calculated measures for the analysis

Create the following five (5) measures within the ‘Key Measures’ table using the DAX statements provided. Apply correct programming protocols and techniques when typing in the DAX statements into Power BI’s formula bar.

Measure 1

Cost($) from TopN% Products = 
VAR ProductPercent = [Distinct Products] * [TopN% Value] // Calculates the percentage of products using the Top N parameter selection
RETURN
 CALCULATE([Total Manufacturing Cost],
    FILTER(VALUES('Financials'[Product ID]),
        RANKX(VALUES('Financials'[Product ID] ), [Total Manufacturing Cost], , DESC ) <= ProductPercent ) )
      

Note: Change the format of this measure to 'Currency'.

Measure 2

Cost(%) from TopN% Products = DIVIDE([Cost($) from TopN% Products], [Total Manufacturing Cost])
      

Note: Change the format of this measure to 'Percentage'.

Measure 3

Product-Cost Cumulative Percentage = 
VAR ProductCost = [Total Manufacturing Cost]
VAR AllFinancials = CALCULATE([Total Manufacturing Cost], ALLSELECTED(Financials))
RETURN
DIVIDE (
SUMX (
    FILTER(
        SUMMARIZE( ALLSELECTED(Financials), 'Financials'[Product ID],
            "Cost", [Total Manufacturing Cost]),
                [Cost] >= ProductCost),
                    [Cost]),
    AllFinancials, 0)
      

Note: Change the format of this measure to 'Percentage'.

Measure 4

Product Rank = RANKX (ALLSELECTED('Financials'[Product ID]), [Product-Cost Cumulative Percentage], , ASC)
      

Measure 5

No. of Selected Products from TopN% = [Distinct Products] * [TopN% Value]
      

Note: Change the format of this measure to 'Whole number'.

Task 5 – Record numerical data in the dashboard

Use ‘Card’ visuals to display the following key measures relevant to the analysis and format according to the following specifications.

• [Total Manufacturing Costs] – change the name to display on the visual as ‘Total Costs’.
• [Cost($) from TopN% Products] – change the name to display on visual as ‘Cost ($) of Top N’.
• [Cost(%) from TopN% Products] – change the name to display on visual as ‘Cost (%) of Top N’.
• [No. of Selected Products from TopN%] - change the name to display on visual as ‘Top N Products’.

Task 6 – Visualise sorted data in a table

Create a summary table using the following specifications.

CHECKPOINT

The following screenshot demonstrates how the summary table is configured to display data from the required fields.

Task 7 – Create the Pareto chart

Create a Pareto chart to showcase the ‘Product’ impact on ‘Profit’, using the necessary visualisations, data fields and previously created calculated measures according to the following specifications.

CHECKPOINT

The following screenshot demonstrates how the Pareto chart is configured and formatted to display data from the required fields.

Task 8 - Create a scatter chart

Create a ‘Scatter’ chart to show the relationship between [Product Rank] vs. [Product-Cost Cumulative Percentage] according to the following specifications.

CHECKPOINT

The following screenshot demonstrates how the Scatter chart is configured and formatted to display data from the required fields.

Task 9 - Contextualise the report.

Report context: The management wants to only focus on data from the last year (assume that this is 2021 in the given dataset).

Task: Use the ‘Select year:’ slicer to select only the required year.

Task 10 - Generate summaries from the chart visuals

Generate a summary (smart narrative) of the data presented in the ‘Pareto chart’ visual for the ‘Cost’ data. To do this:

• Step 1: Right-click on the ‘Pareto chart’ visual and select ‘Summarise’.
• Step 2: Place this visual below the multi-row card visual, above the ‘Pareto chart’.

You can also use the scatter chart to generate a summary. Then, copy and paste the auto-generated text into the first smart narrative visual to combine this information.

Format Specifications: Change the background colour of the smart narrative visual to ‘#E8EBE7’. (Hint: General > Effects). Apply standard formatting for the titles of visuals.

CHECKPOINT

The dashboard with its default settings should look like the following. We will further learn to use this dashboard to conduct a Pareto Analysis and relate it to operational decision making requirements in the next section of this topic

Identifying key insights and providing recommendations

Ethical considerations when reporting analysis results

Ethical considerations arise when deciding which results to include in a report. Presentations of key outcomes and results of the analysis should report results in a fair, objective and neutral manner.

Therefore, it is important that analysts should document both good and bad results. In situations where the outliers detected are genuine and are not due to errors, analysts should develop reports to present the analytic results with outliers as well as without outliers.

Let us implement this using the ‘Pareto Analysis’ dashboard created in the previous practical.

Writing summaries of key insights

When writing a summary based on a completed analysis report or dashboard, the general process is as follows.

• First, describe the context of the analysis.
• Then, outline the numerical data that supports the operational decision-making.
  • State what percentage of causes (e.g. products, customers) resulted in what percentage of the total value (e.g. sales, profit, costs, margin).
  • State outcomes with the effects of outliers and without the effects of outliers.

Use data storytelling to provide recommendations

When providing recommendations, it is important to apply logical thinking and consider aspects of the business operations, which will greatly differ in each analysis.

Analysts basically need to tell a story using the data whilst applying context and relating to the problem.

When writing a summary of the recommendations based on analysis outcomes, the general process is as follows.

• First, briefly outline the operational decision-making requirement.
• Then, state your recommendation providing any numerical figures relevant to the analysis.
• Finally, provide logical reasoning behind the recommendation (may include a comparison of numerical figures to convince the reader).

Practical activity 9: Using the Pareto Rule to support operational decision-making

Let us now test the Pareto rule using the 'Pareto Analysis Dashboard' created in the previous practical.

Task 1 – Adjust the parameters in the dashboard

To test the Pareto rule, we need to use the numeric range parameter ‘Top N %’ to vary the selection of the top portion of products and evaluate the ‘Cost(%) of Top N’ value displayed in the card visual.

Open the Power BI Desktop file from the previous practical activity 'Demo6 -Pareto Analysis', and do the following. 

• Increase the ‘Top N%’ to approximately 20% and evaluate the value displayed in the ‘Cost(%) of Top N’ card visual.
• Increase the ‘Top N%’ value until the ‘Cost(%) of Top N’ displays a value close to 80%.

The following screenshot shows the analysis parameter selection.

As you can see from the analysis, for all 2021 data from the 'Financials' dataset, the top 32.2% of products (that is a total of 103 items) are contributing to 80.05% of the total manufacturing costs.

Task 2 – Change the context of the report (e.g. without outliers)

Let us evaluate this further by doing the following:

• De-select ‘Outliers’ from the ‘Select Cost data:’ slicer to exclude the outliers from the cost data.
• Adjust the ‘Top N%’ value until the ‘Cost(%) of Top N’ displays a value close to 80%.

The following screenshot shows the new analysis parameter selection.

The revised analysis shows that after excluding any outliers in the data, the top 34.8% of products (that accounts for 110 items) are contributing to the 80.11% of total manufacturing costs.

Task 3 – Another approach (e.g. including outliers whilst ensuring its impact on the decision is minimal)

Further to this observation, keeping the revised Top N as 34.8% let us, re-select the outliers into the analysis to see what effect it would have.

This revised analysis will then indicate, whilst ensuring the effects of the outliers are minimal if we still need to include all data in the analysis, the analysis represents the following information.

Including outliers in the data whilst maintaining the top N as 34.8% (which accounts for 111 products) shows that the Top 111 products contribute to 82.24 % of total manufacturing costs.

Task 4 – Write a summary of key insights from the analysis to support operational decision-making

Based on the completed ‘Top N Product Costs: Pareto Analysis’ dashboard page write a paragraph summarising the key findings of the analysis.

Task 5 - Highlighting key insights and figures related to operational decision-making

When providing recommendations, it is important to highlight the decision points and important numerical figures using the visuals in the dashboard. For example, let’s highlight the 80% mark of the product-cost cumulative value and the top N number of products in the scatter chart using constant lines.

Step 1: Add a ‘Y-axis’ constant line to indicate the 80% mark of the cumulative product-cost value in the chart.

Hint:

• Select the ‘Further analysis’ icon (i.e. the third main icon/tab) in the ‘Visualisations’ pane.
• Expand the ‘Y-Axis Constant Line’ > click on ‘Add line’. In this new line added, configure the following:
  • Value: 0.8, Color : #DE6A73, Transparency: 0%
  • Data label: turned ‘On', Colour: #DE6A73

The following screenshot demonstrates how the Y-axis constant line is added to the chart and its format options.

Step 2: Add an ‘X-axis’ constant line to indicate the range of items to be selected for production cost optimisation against the ‘Product Rank’ in the scatter chart.

Hint:

Expand the ‘X-Axis Constant Line’ > click on ‘Add line’. In this new line added, configure the following: -

• Value: 110, Color: #0A7411, Transparency: 20%
• Data label: turned ‘On’, Colour: #0A7411

The following screenshot demonstrates how the X-axis constant line is added to the chart and its format options.

CHECKPOINT

Once these steps are done correctly, you should get the following result as shown in the screenshot.

Task 6 – Outline recommendations

Write a paragraph outlining your recommendations on the operational decision based on the insights from the analysis.

Topic summary

Congratulations on completing your learning for this topic Interpret big data sources and summaries.
In this topic, you learnt how to:

• use Power BI Desktop to access data sources and summaries
• apply descriptive statistics and insight analysis
• use findings from statistical analysis
• identify insights relevant to operational decision-making requirements.

Assessments

Now that you have completed the basic knowledge and skills in this topic, you are ready to commence working on the following assessment event.

• Assessment 4 (Project) – Parts A to D

What’s next?

Next, we will learn how to identify business requirements relating to big data.