Using the AI in Power BI to do root cause analyses

How can finance functions add more value? It’s a challenge accountants and finance professionals in business and industry increasingly face.

One way to boost efficiency and productivity is by speeding up financial root cause analyses with artificial intelligence (AI) tools in data analytics software Microsoft Power BI. These tools can do in minutes what takes hours with Microsoft Excel. But switching gears can be daunting, so here’s an interactive, step-by-step example to get you started.

Imagine we’re accountants in the finance department of Pro Flight Grips Inc., a fictitious company that manufactures grips for recreational products for customers across North America. The company has three product lines — plastic, cord (rubber with cord fabric for moisture absorption), and regular (rubber) — and it manufactures and sells the following products: golf grips (full-cord, half-cord, and cord grips for putters; regular swing club grips; and grips without seam), ski handles, and tennis racket grips.

While preparing financial statements for top management, we notice a significant drop in sales in a month that typically shows more robust demand. How do we quickly determine the underlying cause of this problem in anticipation of management’s questions?

Historically, this question might have taken hours or days to investigate. Now, data analytics assisted by AI can quickly provide a summary and detailed analysis in a fraction of the time.

In the example of Pro Flight Grips, we identify possible causes at a high level with the help of Power BI’s AI “Analyze” feature, then investigate further at detailed levels using Power BI’s “Decomposition Tree.” We include the use of Power BI’s “Key Influencers” to determine possible means of recovery and conclude with the use of the “Smart Narrative” feature to quickly summarize various findings.

While this article uses the AI capabilities found in Power BI, other data analytic products such as Tableau, MicroStrategy, and Qlik may work as well.

GETTING STARTED WITH POWER BI

BI In a real-life scenario, we would start the analysis by importing data into Power BI (see how in the March 2020 JofA article “Power BI: An Analytical View”). To follow along in this walk-through, do the following:

■ Download the file  “Power BI Income Statement Data Example.pbix.” (If you have trouble downloading this file, try right-clicking on the link and selecting the “Save link as…” or Download File option.) 

■ Open Power BI Desktop. (A free version of Power BI Desktop is available, if necessary.)

■ Open the sample data file.

• Click the ”Open” icon (shown in the red circle in the screenshot ”Opening the Sample Data File in Power BI”).
• Find and open the downloaded Power BI file. To do so, look at the bottom of the “Open” section and click ”Browse this device” (shown in the red box in the screenshot ”Opening the Sample Data File in Power BI.”) to access the file explorer.

PROBLEM IDENTIFICATION

Step 1. Data review

Upon opening the Power BI file, the income statement data appears as loaded in a matrix visualization (see the screenshot ”Matrix Visualization With Income Statement Data.”)

To better examine the income statement data, a portion of the visualization (see the screenshot ”Matrix Visualization With Income Statement Data”) has been included and enlarged due to the limited size shown in the screenshot. (See the screenshot ”Partial Income Statement Data.”)

 

Step 2 — Analyze sales

To conduct a quick analysis of sales, create a line chart visualization of product sales by month.

Here’s how:

■ Click on an open space in the Power BI canvas area (the blank space below the income statement as shown in the screenshot “Matrix Visualization With Income Statement Data”).

■ Click on the line chart visualization icon outlined by the small red box (see the screenshot “Matrix Visualization With Income Statement Data.”)

■ From the “Data” pane, left-click and drag the “Mnth” field (located in the “Transactions” table) and release it in the “X-axis” cell located in the “Visualizations” pane.

■ Left-click and drag the “Amount” field and release in the “Y-axis” cell.

■ Left-click and drag the “Account” field and release it in the “Filters” pane in the “Add data fields here” under the “Filters on this visual” block.

■ Under “Filter type” select “Basic filtering” and select “Acct 5000 Product Sales.”

The resulting line chart shows a significant decline in month 5, which is May. The line visualization tool in Power BI prefers to use month numbers on the axis. (See the screenshot “Line Chart Visualization.”)

‘ANALYZE’ FEATURE

Step 1 — Investigate the decrease in sales

To determine the cause of the sales drop, we first turn to Power BI’s “Analyze” feature — the fastest and perhaps most powerful AI tool in Power BI.

To “Analyze” factors affecting sales:

■ Scroll over the point designating Month 5, right-click on the point, then left-click on “Analyze” (as shown in the leftmost red box in the screenshot “Line Chart Visualization — Month 5 observation”).

■ Left-click on “Explain the decrease” (as shown in the rightmost red box in the screenshot “Line Chart Visualization — Month 5 Observation”).

Power BI’s AI engine returns information called “Insights.” They are composed of categories that influence the selected point the most with respect to data provided to Power BI. These “Insights” quickly help us explore the root cause of the Pro Flight sales decline with information for data analysis purposes.

In the example the insights returned include Sum of Amount (Sales):

■ “By Mnth [month] and Product Line,”

■ “By Mnth and Product,”

■ “By Mnth and Day,”

■ “By Mnth and Customer,”

■ “By Mnth and Address,”

■ “By Mnth and Sales Person,”

■ “By Mnth and State,”

■ “By Mnth and Country,”

■ “By Mnth and Cust[omer] Type,” and

■ “By Mnth and Discount Code.”

Note the summary information shown in the green box at the top of the “Insight Prompt of the ‘Analyze’ Feature” screenshot indicates “… the analysis of the 22.45% decrease in Sum of Amount between [month] 4 and 5.”

■ (“By Mnth and Sales Person”) Judy shows the largest decline by salesperson of $39 million.

■ (“By Mnth and State”) Ontario shows the most significant decline for state (region) of $15 million.

■ (“By Mnth and Country”) the U.S. shows the largest decline for countries of $54 million.

While other insights exist, these would be considered the most relevant for decision-making purposes.

‘DECOMPOSITION TREE’

Step 1 — Select the visualization

While the “Analyze” feature quickly provided a high-level analysis, we can further discover how the “Insights” relate to one another by using a “Decomposition Tree.” This visualization will provide a more detailed explanation of root causes in May’s sales decline.

Let us investigate by first adding another page to our Power BI analysis. To add “Page 2,” click the “+” in the purple square to the right of “Page 1” as shown at the bottom of the screen in the “Insight Prompt of the ‘Analyze’ Feature” screenshot.

While the $75,495,418 million decline in sales from April to May has been quickly analyzed using insights from the “Analyze” function, a “Decomposition Tree” will provide further information as well as aid in determining answers to the primary cause of the May sales decline. Power BI’s “Decomposition Tree” visualization provides the ability to “decompose” or break down fields added to the visualization. Since we learned that customer Canadian Golf and Tennis had a significant decrease in sales in May, let’s further analyze what product line, product, and salesperson were responsible for the decline.

To prepare a “Decomposition Tree”:

■ Click on the Page 2 blank canvas.

■ Click on the “Decomposition

Tree” icon (see red square in the screenshot “Page 2 ‘Decomposition Tree’”). Note the “Decomposition Tree” visualization now appears on the canvas area (outlined in the blue box on the “Page 2 ‘Decomposition Tree’” screenshot).

■ Click on the blank “Decomposition Tree” visualization shown in the blue box.

■ Left-click on the bottom right angle shown in the leftmost green circle and extend the visualization to the bottom right area to cover the canvas area.

Step 2 — Selecting fields to be decomposed

Next, we define the visualization for analysis by selecting the fields to be decomposed for further investigation:

■ Click on the right arrow (shown in the orange circle of the “Page 2 ‘Decomposition Tree’” screenshot) located to the left of the “Transaction” file name in the “Data” pane, to display the fields for the “Transaction” file.

■ Next, left-click, drag, and drop “Amount” from the “Transaction” file directly into the “Decomposition Tree” visualization located on the canvas. The “Decomposition Tree” visualization will show “Sum of Amount.”

Continue to add fields directly to the “Decomposition Tree” visualization by:

■ Left-clicking, dragging, and dropping each of the following tables into the “Decomposition Tree”:

• From the “Transactions” file, the tables “Month” and “Sales Person,”
• From the “Customer” file, the tables “Cust Type” and “Customer,” and
• From the “Product” file, the tables “Product Line” and “Product.”

Step 3 — Filter the data

The next step in this process is to filter the fields selected to include only sales data. To filter the sales amount for May:

■ Left-click, drag, and drop the “Account” field (from the “Transaction” file) into an “Add data fields here” box located under the “Filters” pane as shown by the green box in the screenshot “Decomposition Tree Visualization.”

■ Move your cursor over the “Account” filter and click on the down arrow as shown in the screenshot in the green star. Select “Basic Filtering” and click the box to the left of the “Acc 5000 Product Sales” field.

Your visualization should appear as shown in the “Decomposition Tree Visulaization” screenshot (with the exception of the gray box explained below):

Note that the “Amount” field appears as “Sum of Amount” in the “Analyze” section highlighted in yellow in the “Visualizations” pane. In contrast, the “Month,” “Cust Type,” “Customer,” “Product Line,” “Product,” and “Sales Person” fields appear under the “Explain by” section highlighted in green and in the “Filters” pane along with the “Account” and “Sum of Account” fields under “Filters on this visual” highlighted in blue.

Step 4 — Decompose selected fields

Because we know from the “Insights” section that customer Canadian Golf and Tennis had a significant decline in May sales, let’s investigate the root cause of this customer activity for specific products and salespeople associated with them.

To accomplish this:

■ Click on the “+” sign shown in the green circle to the right of the blue bar, which is over “Sum of Amount” in the canvas area. A table of fields to select will appear as shown in the “Decomposition Tree Visualization” screenshot in the gray box. Selecting one of these fields allows us to drill down or “decompose” the detail to a lower level. This “decomposition” can be repeated until all fields have been used.

■ Continue by clicking the “Month” field circled in yellow. The results to this point are shown in the “Decomposition Tree Expanded to Analyze Observed Data” screenshot.

From the results, follow the below steps:

■ Find “Mo 4 April” and click on the “+” circled in green in the “Decomposition Tree Expanded to Analyze Observed Data” screenshot, to the right of the blue bar over the top of its title.

■ Click on the “Customer” field.

■ From the results, find “Canadian Golf and Tennis” and click on the “+” to the right of the blue bar over the top of its title.

■ Repeat this process by:

• Selecting the “Product Line” field, clicking “+” beside “Cord,”
• Selecting “Product”, and finally clicking “+” beside “Full Cord,”
• Selecting “Sales Person.”

Step 5 — Analyze the Decomposition Tree

At this point, we have an expanded Decomposition Tree that breaks down each of the selected fields into the composition of additional fields. Based on this detail, we can analyze the information for April and compare it to May very easily. (See the screenshot “Decomposition Tree Expanded to Analyze Observed Data.”)

From this visual, we can see that Jane was the salesperson assigned to Canadian Golf and Tennis, and full cord sales for April were $5,270,848 (scroll your mouse over Jane’s blue bar to see the white box detail). To compare to May, click on the “Mo 5 May” selection under the “Month” column, where we find Jane’s sales of full cord to Canadian Golf and Tennis were $1,457,672 (shown in the green box of the “Decomposition Tree — Comparison Mo 4 April to Mo 5 May” screenshot) – a decrease from April to May of 5.1% of the total decline.

While not shown, the same model can be used to determine the impact of sales lost from the cord putter product by clicking on “Mo 4 April” under the “Month” field column, followed by clicking on “Cord Putter” under “Product.” Jane is the salesperson for this product, with sales in April of $10,919,932. Clicking on “Mo 5 May” under the “Month” field column, we learn Jane’s sales for May for cord putter were $6,186,016 — a decrease in cord putter sales in May by $4,733,916, representing 6.3% of the total sales decline in May.

This analysis shows that the root cause of the sales decline was this account. We then would want to inquire of the account’s salesperson, Jane, as to what happened and why the sales dropped.

FINDING IDEAS TO RECOVER LOST SALES

Once we as Pro Flight’s accountants determine the underlying causes of the problem and begin work to address them, the need for possible means of recovery may be desirable. A way to discover impacts either to increase sales or decrease sales is to use Power BI’s “Key Influencers” feature.

Power BI’s “Key Influencers” visualization is a robust feature that aids understanding of how attributes drive (or “influence”) a specific attribute. This visualization analyzes data and explains their impact as significant factors affecting the data as individual “Key Influencers” and groups (described in Power BI) as “Top Segments” and their respective influencers.

The visualization is customizable, allowing users to filter the visual and show the remaining values considered influencers by virtue of Power BI’s AI selecting key data.

In the following example, we will use “Key Influencers” to determine “What (attributes) influence (sales) amount to increase?”

To construct a “Key Influencers” visualization by:

■ Adding another page (click the “+” to the right of “Page 2” at the bottom left of the screen to add “Page 3” as illustrated previously).

■ Click on the blank canvas area. Select and click on the “Key Influencers” visualization icon under the “Visualization” pane shown circled in green in the “Decomposition Tree — comparison Mo 4 April to Mo 5 May” screenshot.

■ Expand the visualization area as done previously by left-clicking on the right angle at the bottom right of the blank visualization and drag down and to the right. (Refer back to the “Page 2 ‘Decomposition Tree’” screenshot, if needed). As changes are added, the “Key Influencers” begins to take shape. (See the screenshot “Construction of Key Influencers Visualization.”) Because the goal is to “Analyze” (sales) amount, left-click, drag, and drop the “Amount” field under the “Transactions” file and release in the “Analyze” box reading “Add data fields here” located in the “Visualizations” pane (shown in the screenshot “Construction of Key Influencers Visualization” with the orange line and arrow). 

■ To select the attributes by which sales amount will be influenced, leftclick, drag, and drop the “Product” and “Product Line” fields from the “Product” file to the “Explain by” box (shown by the blue arrows in the screenshot “Construction of Key Influencers Visualization”).

■ Repeat the process to click, drag, and drop the:

• “Sales Person” field from the “Transactions” file.
• “Cust Type” and “Customer” fields from the “Customer” file.
• The “Account” field from the “Transaction” file to the “Filters” pane under “Filters on this visual” in a blank “Add data fields here” box (shown in the screenshot “Construction of Key Influencers Visualization” with the curved red line).
• Select “Basic filtering” under “Filter type.”
• Click on “Acct 5000 Product Sales” to filter the data. (Since we are analyzing influencers on all sales, we will not restrict the visualization to the month of May.)

The “Key Influencers” visualization is shown in the “Key Influencers Visualization — Top Segments” screenshot. Note the visualization created shows “What influences Amount to” either “increase or decrease” Amount. On the left side of the visualization, under the “When” column shown in the green box, find “Product is Full Cord” and click on the circle that shows “190.4K” to the right of the arrow on the same line. The resultant graph to the right shows “Full Cord’s” impact on increasing sales. The visualization indicates “When Product is Full Cord the Average of Amount Increases by 190.4K.” Other “influencers” are also shown.

In summary, through using Power BI’s built-in AI, we could determine:

■ Canadian Golf and Tennis was the customer with the largest decline in sales between April and May (using the “Analyze” feature).

■ The product line and product from which Canadian Golf and Tennis showed the greatest sales reduction were “Cord” and “Full Cord,” respectively.

■ The most profitable ways in which we could compensate for a future reduction in sales would be to market “Full Cord” product to customer type “Distributor.”

Note that other observations and interpretations could also be made using the previous analysis.

SMART NARRATIVE

Assisting with the writeup

Power BI can quickly provide a text summary of all visualizations on a page or a selected visualization on a page using “Smart Narrative” to explain results in natural language. Using the previous visualization:

■ Select the visualization (left-click) and then right-click. A box appears with several options.

■ Click on the “Summarize” option. Power BI provides two narrative type choices, “Copilot” and “Custom.”

■ Select “Custom” and the summary appears.

■ Resize the key influencers visualization by right-clicking on the tab highlighted in the green circle and dragging to the left. (See the screenshot “Smart Narrative Visualization.”) Position the summary in the blank area created.

For a more detailed example:

■ Return to “Page 1” (by clicking on the “Page 1” tab at the bottom of the screen).

■ Create space on the canvas by sizing down the income statement “Matrix” visualization (click on the visualization, click on the left sidebar, then drag to the left to make an open space — similar to the previous steps to “Resize the Key Influencers Visualization”).

■ Left-click on the open space created, then left-click and select the “Narrative” visualization icon.

■ Select “Custom” and the summary narrative for the line chart appears.

Note that the summary is dynamic and can be modified by selecting different data in the visualizations on the page. (See the screenshot “Smart Narrative Visualization — Sales Summary.”)

While answers to the significant decline in sales from April to May were resolved early on quickly using Power BI’s AI, the examples that followed aid in discovering more facts about what led to the decline as well as to what influences an increase or decrease.

These are just a few examples of powerful AI features that can significantly aid the work of the CPA using data analytics.

---

About the authors

William Stewart Thomas, CPA, CGMA, Ph.D., is a professor of accounting and former vice chancellor, and Dena Dail Breece, CPA, CGMA, Ph.D., is an associate professor of accounting, both at the University of North Carolina at Pembroke. To comment on this article or to suggest an idea for another article, contact Jeff Drew at Jeff.Drew@aicpa-cima.com.

---

LEARNING RESOURCES

Microsoft Power BI: Power BI series

A nine-part self-study online series created to help you develop the skills necessary to use Microsoft Power BI tools.

CPE SELF-STUDY

Dashboarding With Power BI

Presented by Tracy Pound, managing director of Maximity Limited, a Microsoft Dynamics 365 Business Central and Microsoft 365 specialist, this webinar will deliver a practical demonstration of building a dashboard in Power BI.

Oct. 17, 2024

WEBCAST

For more information or to make a purchase, go to aicpa-cima.com/cpe-learning or call 888-777-7077.

---

AICPA & CIMA MEMBER RESOURCES

Articles

“Tips for Using the Power BI Suite of Tools,” FM magazine, Feb. 22, 2024

“BI Tools: Power Query, Power Pivot, and Power BI,” FM magazine, Jan. 4, 2024

“5 Steps to a Digitalized Reporting Dashboard,” FM magazine, Sept. 14, 2022

“Are You Telling a Story With Data? You Should Be,” FM magazine, Sept. 13, 2022

“Power BI: An Analytical View,” JofA, March 1, 2020

JofA AI coverage page