ACCTING2503 · Accounting Systems And Analytics
Data Analytics with Microsoft Power BI
Power BI is Microsoft's self-service business-intelligence tool, and in ACCTING 2503 it is the practical skill behind Test 2. It comes in two connected halves: Power BI Desktop, where you import and clean data (Power Query), model it (relationships and DAX) and design a report, and the cloud Power BI Service, where you publish, share, build dashboards and use AI Q&A. The topic runs from a source (Excel, CSV, web, or an OData feed) through a star-shaped data model to an interactive report, and adds four one-click AI features: anomaly detection, key influencers, the decomposition tree and smart narratives. Note the scope: Power BI is assessed in Test 2 only and is excluded from the closed-book final exam, so learn it to do it, not to write essays about it.
What this chapter covers
- 011. Desktop vs Service - author and model in Desktop; publish, dashboards and Q&A in the cloud Service
- 022. Report vs dashboard - a multi-page report versus a single page of pinned tiles (Service only)
- 033. The end-to-end workflow - Get Data - Power Query - model - report - Publish - Service
- 044. Get Data and Power Query - sources (Excel, CSV, web, OData) and cleaning, typing, merging
- 055. Modelling - relationships (like PK to FK) plus DAX measures and calculated columns
- 066. The dimensional (star) model - one central fact table of measures ringed by dimension tables
- 077. Interactivity - slicers, cross-filtering, drill-down, conditional formatting
- 088. The four AI features - anomaly detection, key influencers / top segments, decomposition tree, smart narratives
Reading an anomaly-detection flag on a daily-revenue line chart
- +3Why it is flagged. Anomaly detection marks any point that falls outside the model's expected (predicted) range, which it computes from the surrounding series. Here $6,180 sits above the upper bound of $3,510, so it is outside the band and flagged - it is the band, not a fixed threshold or the average, that the point is tested against.
- +3Prerequisites / limitations. The visual must be a line chart with a time series on the Axis and at least 4 data points. It does not work when the chart has a legend, multiple or secondary values, or a Forecast / Min / Max / Average / Median / Percentile line, and drill-down is not supported.
- +2Strength and analytic category. Strength = the ratio of the deviation attributable to that field to the total deviation, so Adelaide explains about 92% of the spike - nearly all of it. Plotting revenue is descriptive analytics; the automatic explanation of why the point spiked is diagnostic analytics.
Key terms
- Power BI Desktop vs Service
- Desktop is the free Windows app where you author: import and clean data, build the model (relationships, DAX) and design report visuals. The Service is the cloud platform where you publish, share, build dashboards and use AI Q&A. DAX modelling lives in Desktop; dashboards and Q&A live only in the Service.
- Report vs dashboard
- A report is a multi-page document built on a dataset / semantic model and authored in Desktop. A dashboard is a single page of pinned tiles drawn from one or more reports, and it exists only in the Service.
- OData feed
- A queryable web data source you can connect to in Get Data, alongside Excel workbooks, CSV files, web/HTML pages and databases.
- DAX (Data Analysis Expressions)
- Power BI's formula language for measures and calculated columns, for example Total Sales = SUMX(Sales, Sales[Qty] x Sales[Price]).
- Star (dimensional) model
- A data model with one central fact table holding the measures (quantity, amount) plus foreign keys, ringed by dimension tables (Date, Product, Customer, Region) that describe them. It is simple, fast to query and lets a user slice any measure by any dimension.
- Expected range
- The predicted band that anomaly detection computes from the surrounding time series. Points inside are normal; a point outside the band is flagged as an anomaly.
- Explanatory strength
- For an anomaly or a key influencer, the ratio of the deviation attributable to one field to the total deviation - how much of the effect that field explains.
- Analyze vs Explain by
- In the AI visuals, the metric or outcome goes in the Analyze field and the candidate driver fields or dimensions go in Explain by. Expand by is only for measures / summarised fields.
Data Analytics with Microsoft Power BI FAQ
Is Power BI on the final exam?
No. Power BI is assessed in Test 2 (a practical) only and is excluded from the closed-book final exam. The final covers the Romney topics from Weeks 1-10 plus digital reporting and XBRL. Study Power BI to do the clicks for Test 2, but do not spend revision time on it at the expense of XBRL and the control/systems material when the end-of-semester exam approaches - check your course outline for the current Test-2 format and weighting.
What is the difference between key influencers and the decomposition tree?
Key influencers ranks the factors that drive a metric ('when X, the outcome is N times more likely'), answering which factors matter. The decomposition tree is for interactive drill-down and root-cause exploration - you split a measure by dimensions in any order. A prompt that says 'which factors drive...' points to key influencers; 'let me drill region then product then month' points to the decomposition tree. Do not swap them.
Where exactly does anomaly detection work, and where does it fail?
It works only on a line chart with a time series on the Axis and at least 4 data points. It fails when the chart has a legend, multiple or secondary values, or a Forecast / Min / Max / Average / Median / Percentile line, and it does not support drill-down. These exact limits are a heavily tested point.
Which fields go in Analyze versus Explain by?
The metric or outcome you are studying always goes in the Analyze field; the candidate drivers or dimensions go in Explain by. Expand by is only for measures or summarised fields. Getting these the wrong way round is a common Test-2 mistake.
What is a smart narrative?
A smart narrative is an AI-generated written summary of a visual or a whole report page - trends, takeaways and comparisons in plain English. Its numbers update on every data refresh, it is fully editable, and it can travel into PowerPoint as live text.
Exam move
Test 2 is hands-on, so do the clicks rather than just reading: import an Excel workbook, connect an OData feed, clean the data in Power Query, build one relationship, write a single DAX measure, add a slicer, then switch on Anomalies and drop a decomposition tree. Keep the Desktop-versus-Service split straight, because most traps turn on it - DAX modelling is Desktop, dashboards and AI Q&A are the Service. For the AI features, memorise what each one is for (anomaly = out-of-range time-series points; key influencers = ranked drivers; decomposition tree = interactive root-cause drill-down; smart narrative = written summary) and the anomaly-detection limits (line chart, time axis, at least 4 points, no legend or secondary values). Finally, remember Power BI is Test 2 only and is not on the final exam, so budget your late-semester revision toward XBRL and the Weeks 1-10 control and systems topics.
