Cheat sheet · Visual Analytics

Two columns, no chrome, one section per half. Ctrl+P (Windows) / ⌘P (Mac).

Visual Analytics · Pre mid-sem

Definitions to write verbatim

Visual analytics (Keim et al. 2008) — combines automated analysis techniques with interactive visualizations for an effective understanding, reasoning and decision making on the basis of very large and complex datasets.
Keim's mantra — Analyze first, show the important, zoom, filter and analyze further, details on demand. Note the order: analysis precedes the picture.
DIKW — Data (facts, figures, signals, measurements) to Information (organised, structured, categorical, condensed, calculated) to Knowledge (understanding, integration, applied, actionable, patterns, decisions) to Wisdom (idea, learning, concept, compare, discuss).
Analytics — the scientific process of transforming data into insights for better decisions.
Social Network Analysis (SNA) — a method used to perform visual and mathematical analysis of relationships. Both halves are required.
Noise — a random error, seen as variance in a measured variable. Removed by binning, regression, clustering.
Text data — information expressed through words, sentences or documents. It carries information, but its structure is not immediately visible.
Tokenization — separating text into smaller units called tokens; word, character or subword. Creating the vocabulary is its ultimate goal.
Bag of words — a representation describing the occurrence of words in a document: a vocabulary of known words plus a measure of their presence. All order and structure is discarded.
Document-term matrix — each document is a vector of term weights; the simplest weighting is a count of occurrences.
Data-ink ratio — the proportion of a chart's ink that carries data. Raise it by decluttering and using white space.
The professor's one-liners — Data Cleaning = fixing problems in the data. Data Integration = bringing data from different sources together. Data Reduction = making a large dataset smaller without losing important information.
A visually attractive visualization is not necessarily an analytically informative visualization. And the subject's tagline: detecting the expected, discovering the unexpected.

Network formulas, with the class's conventions

Density (undirected) = e / (n(n-1)/2) Density (directed) = e / (n(n-1))
Degree centrality C_D(i) = d(i) / (n - 1) directed C'_D(i) = d_out(i) / (n - 1)
Betweenness centrality C_B(i) = SUM over j
Closeness centrality C_C(i) = (n - 1) / SUM over j of d(i,j)
Degree prestige P_D(i) = d_in(i) / (n - 1) directed only Proximity prestige P_p(i) = SUM over j in I_i of d(j,i) / |I_i|
Conventions. An unreachable distance is entered as 100. Proximity prestige is a distance, so a lower value is better, unlike every centrality. Prestige is defined on in-links and for directed networks only, and this course treats it as separate from centrality.
Checks. The degree sum equals 2e in an undirected graph. A geodesic matrix is symmetric if and only if the graph is undirected. Density lies in [0, 1] always.
Worked reference (Centrality Question 1, six nodes). Undirected density 6/15 = 0.4; directed 7/30 = 0.2333. Degrees 2, 2, 3, 2, 2, 1 give degree prestige 0.4, 0.4, 0.6, 0.4, 0.4, 0.2. Closeness 0.5, 0.625, 0.625, 0.4545, 0.4545, 0.357. Betweenness C_B(2) = C_B(3) = 6/15, the rest 0.

Cleaning arithmetic

Binning: sort, cut into bins of equal COUNT, then replace by bin mean | bin median | nearest bin boundary (ties go downwards)
Drill 1. 10, 2, 19, 18, 20, 18, 25, 28, 22, bin size 3. Sorted 2, 10, 18 | 18, 19, 20 | 22, 25, 28. Means 10, 19, 25. Medians the same. Boundaries 2, 2, 18 | 18, 18, 20 | 22, 22, 28.
Drill 2. 8, 16, 9, 15, 21, 21, 24, 33, 26, 27, 30, 38, bin size 4. Bins {8,9,15,16} {21,21,24,26} {27,30,33,38}. Means 12, 23, 32. Medians 12, 22.5, 31.5. Boundaries 8,8,16,16 | 21,21,26,26 | 27,27,38,38. (The slide prints 34 for 33; use 33.)
Min-max normalisation x' = (x - min) / (max - min) into [0, 1] Standardisation (z) z = (x - mean) / SD mean 0, SD 1
Missing values. Delete (fine if few, problematic if many) · fill manually from a reliable source · mean if roughly symmetric and outlier free · median if skewed or with outliers. Proof: 20, 21, 22, 23, 100 has median 22 and mean 37.2.
Categorical gaps. Ignore the tuple · most frequent value · classifier algorithm · unsupervised ML. Warnings: not possible for a small dataset, and it leads to an imbalanced dataset.
The eight cleaning techniques. Remove duplicates · remove irrelevant data · standardisation · convert data type · clear formatting · fix errors · language translation · handle missing values.

Design vocabulary the scenario paper uses

Four preattentive attributes — colour, form, spatial positioning, movement. Colour splits into hue (position on the spectrum), saturation (amount of grey) and luminance (black against white). Form covers orientation, size, shape, length, width.
Three memories — iconic (under a second, automatic, processes preattentive attributes), short-term (chunks, about a minute), long-term (repetition, rehearsal, story-telling).
Four Gestalt principles — similarity, proximity, enclosure, connection.
Length beats area. A square three times taller and wider has nine times the area, so people misjudge size and read length well. Never make area the number the reader must extract.
Colour schemes — categorical for unordered categories, sequential for low to high, diverging when a midpoint is meaningful.
Colour mistakes — unnecessary colour · excessive colour · insufficient contrast · inconsistency across related charts · neglecting colourblindness · ignoring the mode of delivery.
Five common mistakes — wrong type of visualisation · too much information · Excel default settings · too many attributes · unnecessary 3-D.
Charts to avoid (Camm) — pie, 3-D, doughnut, radar. Use a table when exact values matter.
Minimise eye travel — align the chart title and vertical-axis title with the vertical axis; put line labels adjacent to and right of the lines; align the horizontal-axis label with the line labels.
Answer skeleton for a scenario question. 1 name the variable types and how many levels · 2 name the question (compare, trend, distribution, relationship, share, exact values) · 3 name the chart and why · 4 name the encoding and the preattentive attribute · 5 name a Gestalt principle · 6 do the data-ink pass · 7 name what you refuse and why.

Text analytics in one block

Five steps of NLP — lexical (morphological) analysis · syntactic analysis (parsing) · semantic analysis · discourse integration · pragmatic analysis.
Six NLG stages — content determination · document structuring · sentence aggregation · lexicalization · referring expression generation · surface realization.
Eight pipeline stages — sentence segmentation · tokenization · stemming · lemmatization · identifying stop words · dependency parsing · NER · chunking.
Three ambiguities — lexical ("bank") · syntactic ("I saw the man with the telescope") · referential ("The doctors met the patients. They were worried.").
Stemming against lemmatization — stemming chops characters and may return a non-word (history, historical give histori; better gives bet or bett); lemmatization uses a corpus and WordNet with the POS and always returns a real word (was gives be, mice gives mouse, better gives good).
Four sentiment types — fine-grained (five polarity bands) · emotion detection (lexicons; "killing it" is the trap) · aspect based ("the battery life of this cellphone is too short") · multilingual.
Four word-cloud limitations — exact values hard to compare · context lost ("cheap") · sentiment missing (slow and fast delivery both feed delivery) · relationships hidden (delivery and late).
Four advanced tokenizers — Byte-Level BPE (GPT-2) · SentencePiece (T5, ALBERT, XLNet) · WordPiece (Google, BERT) · Unigram Language Model (XLNet).

Tools, examined in writing

Gephi columns. The nodes file needs Id and Label; the edges file needs Source and Target. For the Spotify data, rename spotify_id to Id, name to Label, name1 to Source and name2 to Target.
VOSviewer triple. Analysis type (co-authorship, co-occurrence, citation, co-citation, bibliographic coupling) x unit of analysis (authors, organizations, countries; all, author or index keywords) x counting method (full or fractional).
Network map anatomy. Item = label plus circle; item size = weight; item colour = cluster; line = link; line thickness = link strength; distance is approximately relatedness.
Thesaurus file. Two columns, label and replace by, used to merge label variants. It is label cleaning, so it belongs to the data-cleaning family.
Scopus columns to analysis. Authors and Author full names to author co-authorship · Affiliations to organization and country co-authorship · Author Keywords and Index Keywords to co-occurrence · Cited by with DOI or EID to citation · Source title to source co-citation · Year to the overlay visualisation.
Matplotlib. plt.bar / scatter / hist / pie / boxplot then plt.show(). Bar width, color (a string for all, a list for one per bar). Histogram bins=np.arange(...), histtype='step', orientation='horizontal'. Scatter c, s, cmap plus plt.colorbar(), marker, edgecolors. Pie explode, autopct='%1.1f%%', colors. Seaborn box plot: numeric on y for vertical, showmeans=True, meanprops={...}.
Bins from arange. Number of bins = number of edges - 1, and np.arange excludes its stop value.

Formula cards

Mid-value of a class interval

Mid-value = (lower limit + upper limit) / 2

Whenever a frequency distribution has to be summarised, plotted at a point, or used to compute a mean from grouped data.

lower limit: Smallest value the class contains, e.g. 10 in 10-20
upper limit: Largest value the class contains, e.g. 20 in 10-20

Cumulative frequency

CF(k) = f(1) + f(2) + ... + f(k)

For an ogive, and for any "how many are below x" or percentile question. Always check that CF of the last class equals n.

f(i): Frequency of the i-th class
CF(k): Cumulative frequency up to and including class k
n: Total number of observations; CF of the last class must equal it

Percentile from grouped data

Pk = L + ((k/100 x n - CF_below) / f) x c

To read a median (k = 50) or a quartile off a frequency distribution. State the convention you are using; different books place the position at kn/100 or k(n+1)/100.

L: Lower limit of the class containing the percentile
n: Total observations
CF_below: Cumulative frequency of all classes before that class
f: Frequency of that class
c: Width of that class

Pie slice angle

Angle = (category count / total) x 360 degrees

Only when a pie chart is unavoidable. The course recommends a sorted bar chart instead, because length is judged accurately and angle is not.

category count: Frequency of the category
total: Sum of all category frequencies

Density (undirected)

Density = e / (n(n-1)/2)

Whenever a question asks how connected an undirected network is overall. Count the edges first, and check with sum of degrees = 2e.

e: Number of edges actually present
n: Number of nodes, including singletons
n(n-1)/2: Maximum possible edges if every node joined every other

Density (directed)

Density = e / (n(n-1))

For a directed network, where each pair can be joined in both directions, so the maximum is twice the undirected one. Say which formula you are using; the two answers differ by exactly a factor of 2.

e: Number of directed edges (arrows)
n: Number of nodes
n(n-1): Maximum possible directed edges

Degree sum check

SUM of degrees = 2e (undirected) · SUM in-degrees = SUM out-degrees = e (directed)

After counting edges or degrees, before any centrality arithmetic. It catches a missed or double-counted edge in one line.

degree: Number of edges at a node
e: Number of edges in the network

Degree centrality

C_D(i) = d(i) / (n - 1) C'_D(i) = d_out(i) / (n - 1)

To find popular, highly connected nodes. Report in-degree and out-degree separately for transactional data. Both Centrality Question papers use total degree for the directed case; say which you use.

d(i): Degree of node i: the number of edges at it
d_out(i): Out-degree: arrows leaving node i
n - 1: The largest degree any node could have

Closeness centrality

C_C(i) = (n - 1) / SUM over j of d(i,j)

To find the best broadcasters, the nodes that can reach the whole network fastest. Weak discriminator in a dense network, so use it within one cluster. Unreachable distances are entered as 100.

d(i,j): Geodesic distance from i to j, in edges; 100 if j is unreachable
n - 1: Numerator, so a node one step from everyone scores 1

Betweenness centrality

C_B(i) = SUM over j < k of p_jk(i) / p_jk

To find bridges and gatekeepers who control flow between clusters. Neither j nor k may be i. Normalise by the number of pairs and state which pair set you used.

p_jk: Number of shortest paths between j and k
p_jk(i): How many of those pass through i
pairs: C(n,2) = n(n-1)/2 pairs in total; the deck instead uses the pairs excluding i

Degree prestige

P_D(i) = d_in(i) / (n - 1)

Directed networks only. Raw popularity: how many nodes point at you. The number of followers, not the number you follow.

d_in(i): In-degree: arrowheads pointing at node i
n - 1: The most in-links any node could have

Proximity prestige

P_p(i) = SUM over j in I_i of d(j,i) / |I_i|

Directed networks. Accessibility: how close the nodes that can reach you are. A mean distance, so LOWER is better, and it must be quoted alongside |I_i|.

I_i: The set of nodes that can reach i by a path of any length
|I_i|: How many nodes are in that reaching set
d(j,i): Shortest distance from j to i, following the arrows

Unreachable-node convention

d(i,j) = 100 when j cannot be reached from i

In any closeness or distance-matrix calculation on a directed network. The worked solution writes "Use – as 100". Some texts use n instead; state your choice, and expect the score to collapse towards zero.

100: The stand-in for an infinite distance used by this class
d(i,j): Geodesic distance from i to j

Grouped bar offsets

position of series k = x + (k - (m + 1) / 2) * width, painted width = m * width

When several series share each category slot. Keep m x width below 1 so the groups stay separated, and restore the category names with plt.xticks.

x: Category centres, usually np.arange(number of categories)
m: Number of series (bars per group)
k: Which series, counting from 1
width: Bar width in x-axis units; Matplotlib's default is 0.8

Histogram bin count from np.arange

edges = np.arange(start, stop, step); bins = number of edges - 1 = ceil((stop - start) / step) - 1

Whenever a histogram question gives bin edges rather than a bin count. Remember np.arange excludes its stop value, so push stop past the last edge you want.

start: First bin edge, included
stop: Excluded upper limit
step: Bin width

Pie slice share and angle

share = value / total; percent printed by autopct = 100 * share; angle = 360 * share

For any pie-chart question, and for the argument against pie charts: convert two slices to degrees and show how close they are.

value: One category's size, as passed to plt.pie
total: Sum of every value in the list
autopct: Format string, e.g. '%1.1f%%', that prints the percentage

Bin mean, bin median, bin boundary

sort, cut into bins of equal count, then replace every value by the bin's mean, or its median, or its nearer extreme

Whenever a question says 'smooth by binning' or gives a bin size. Bins hold an equal count, not an equal width. Ties on the boundary rule are broken downwards in the class's worked slides.

bin size: Number of values per bin, given in the question
bin mean: Average of the values in that bin; replaces all of them
bin median: Middle value, or the average of the two middle values for an even count
bin boundary: The bin's smallest and largest values; interior values move to whichever is nearer

Min-max normalisation

x' = (x - min) / (max - min) maps into [0, 1]

When several series of different magnitudes must share one axis, or when a visual encoding needs a bounded input such as colour intensity or marker size. Sensitive to the extremes, so one new record can rescale everything.

x: The original value
min, max: Smallest and largest values in that column
x': The normalised value; the minimum becomes 0 and the maximum becomes 1

Standardisation (z-score)

z = (x - mean) / SD gives mean 0 and SD 1

When the question is whether several series move together, or when outliers make a min-max range unrepresentative. Produces negative values, so it cannot feed a visual that needs non-negative numbers.

x: The original value
mean: Average of the column
SD: Standard deviation of the column
z: How many standard deviations above (positive) or below (negative) the mean the value lies

Visual Analytics · Post mid-sem

Read this first

Check the whole of this page against your class slides. No Power BI material of any kind exists in your Drive folder or in either section's Classroom export: no decks, no .pbix files, no notes and no announcements after 10 September 2026. Everything below is built from the outline's own session wording plus the Camm textbook plus general knowledge.
The exam is pen and paper and not Excel based. Power BI is examined in writing: name the view, the pane, the setting or the function, say what it does, and explain the consequence of getting it wrong. Write DAX by hand; a wrong function costs the mark, a missing bracket does not.
The outline's one prerequisite is understanding primary key and foreign key. That is the data model, and it is the most likely written question in this half.

Structure and vocabulary

Three products. Desktop to author · Service to publish, share, schedule refresh and hold dashboards · Mobile to consume.
Report against dashboard. A report is authored in Desktop over one dataset and may have many pages. A dashboard exists only in the Service, is one page of pinned tiles, and can therefore span several datasets.
Three views. Report (the canvas) · Data or Table (the loaded rows, and where calculated columns usually go) · Model (tables and relationships).
Four panes. Fields (what exists) · Visualizations (the gallery and the field wells) · Filters (the filter cards) · Format (titles, axes, legends, labels, colours).
Get Data. Excel, CSV, database, web. Import loads a compressed copy and needs a refresh; DirectQuery queries the source live. Always choose Transform Data over Load.
Filter hierarchy, narrowest first. Visual · page · report · drill-through. They compose, so a Top 5 inside a report filtered to 2026 is the top 5 of 2026. A slicer is not a level: it is a visual that lets the reader set a filter.
Interaction. Cross-highlighting keeps a visual's full shape and shades the selection; cross-filtering reduces it to the selection. Set per pair under Edit interactions.
Drill-down moves through a hierarchy inside one visual. Drill-through opens a different page filtered to the item you right-clicked.

The data model

Primary key — unique in its table, identifies one row. Foreign key — the same values in another table, repeated. A relationship is a line between exactly those two columns.
Star schema — one long fact table (one row per event, the additive numbers, the foreign keys) surrounded by short dimension tables (one row per thing, the primary key, the descriptive columns).
Cardinality — one-to-many (normal, dimension to fact; the one side is where the key is unique) · one-to-one (usually should be one table) · many-to-many (replace with a bridge table joined one-to-many to both sides).
Cross-filter direction — single (from the one side to the many side; the default and the safe choice) or both (risks ambiguous filter paths).
Symptoms. Every bar the same height at the grand total means a missing or wrongly directed relationship. A total slightly too high means a duplicate key on the lookup side. A row count that fell after a merge means an Inner join silently dropped unmatched rows; diagnose with a Left Anti Join.
Add a Date table and mark it as the date table, with Year, Quarter, Month and Month Number columns. Without it there is no time intelligence, and months sort alphabetically.

Power Query

Applied Steps is the ordered, editable recipe: repeatable on next month's file, documented by construction, and breakable if you delete a middle step. It never edits the source.
Order of work. Remove top rows · promote headers · remove bottom rows · fill down · replace values then change type · remove or replace errors · unpivot other columns · rename and type the new columns · trim and clean · remove duplicates on the key · Close and Apply.
Unpivot Columns turns headers into an Attribute column and cells into a Value column. Prefer Unpivot Other Columns, which names the columns to keep. Checks: rows after = rows before x unpivoted columns, and the Value column must sum to the original value area.
Append is UNION (stacks rows, needs matching names, gets taller). Merge is JOIN (brings columns on a key, gets wider). Join kinds: Left Outer (default, keeps all of the first table) · Inner (matches only, loses the rest silently) · Full Outer · Right Outer · Left and Right Anti (the unmatched rows only, for diagnosis).
Group By needs the grouping columns, a new column name, and an aggregation: Sum, Average, Median, Min, Max, Count Rows, Count Distinct Rows. It is data reduction, so the grouping level must match the questions.
Remove Duplicates compares the selected columns. Select the key column alone for one row per key; select all columns and only exact copies go.
Caveat. The preview is a sample, so a step can look right and fail at Close and Apply.

DAX

Calculated column — computed at refresh, row by row, in row context, stored, does not respond to filters, usable on an axis, in a legend or in a slicer.
Measure — computed at query time, once per cell, in filter context, stored nowhere, responds to every filter, usable only in Values and Tooltips.
The rule. A column if it must be sliced by; a measure if it must aggregate and react. Default to a measure.
Total Sales = SUM ( Sales[Sales] ) Total Profit = SUM ( Sales[Profit] ) Total Cost = [Total Sales] - [Total Profit] Profit Margin Ratio = DIVIDE ( [Total Profit], [Total Sales], 0 )
Format the ratio as a percentage. Margin plus cost ratio always makes 100%, which is a free check.
SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] ) iterate, then add SUM(a) * SUM(b) is NOT SUMX(T, a*b) cross terms
Ratios are not additive. Overall ratio = SUM(numerators) / SUM(denominators), which is a size-weighted average. The plain average of the ratios is a different number: 5%, 18%, 15% and 0% average to 9.5% where 155,000 / 1,200,000 is 12.92%.
CALCULATE ( expr, filter... ) the only way to change filter context Sales All = CALCULATE ( [Total Sales], REMOVEFILTERS ( Customer[City] ) ) Share = DIVIDE ( [Total Sales], [Sales All] )
Sales LY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) YoY Growth = DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] ) Running = CALCULATE ( [Total Sales], FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )
COUNT takes a column and skips blanks; COUNTROWS takes a table and counts every row; DISTINCTCOUNT for orders spanning several lines.

Maps, slicers and visuals

Data Category must be set before any map appears: Address, Place, City, County, State or Province, Postal Code, Country or Region, Latitude, Longitude.
Map (bubbles at points) · Filled Map (the choropleth, area colour) · Shape Map (custom boundaries from a map file) · ArcGIS (base maps, reference layers, drive time).
Ambiguous places. Fix with a Country to State to City hierarchy in the Location well, a merged place string, or Latitude and Longitude, which is the only fully reliable option.
Choropleth warning. Ink follows geographic area, not the measure, so map a rate not a total, put the ranking in a sorted bar chart beside it, or use a cartogram and admit the trade-off.
Slicer styles. List (few values) · Dropdown (many) · Between or slider (numeric and date ranges) · Relative date (last 30 days, computed from today so it never goes stale). Sync slicers has two independent checkboxes per page: Synced and Visible.
Filtering. Basic (tick values) · Advanced (contains, starts with, is greater than, is on or after, joined by And or Or) · Top N (visual level only, computed inside every other filter, so the members change with the slicers) · filter on a measure (drops the category values whose measure fails the test).
Question to visual. Compare across categories: sorted bar or column. Trend: line. Relationship: scatter. Share: stacked bar for a few parts, or a table. Exact values: table or matrix. One number in context: KPI. Geography: filled map for areas, map for points.
Editing pass. Gridlines off · data labels on and the value axis off, never both · legend off for one series or replaced by direct labels · title states the finding, not the field name · sort by the measure.
Sort by column for ordered text: display Month Name, order by a hidden Month Number. Needs one sorting value per displayed value.
Tooltips. Field tooltips for extra measures on hover; a report page tooltip is a page sized as Tooltip showing a mini report. Themes fix the palette once, preventing inconsistency across related charts. Accessibility: colourblind-safe palette, a second cue beside colour, alt text, contrast checked as projected.

Dashboards, storytelling and honesty

Data dashboard — a collection of visual displays of the data an organisation needs to achieve its objectives. A KPI is a measure management has decided is critical.
Three taxonomies. Data updates: static (manual, infrequent, slow KPIs) or dynamic (regular new data, frequent). User interaction: noninteractive or interactive. Organisational function: operational (lower level managers, real time) · tactical (mid-level, strategy support) · strategic (executives, recurring) · analytical (analysts, trends and prediction).
Four interactivity features. Drilling down · hierarchical filtering · time interval widget · customization tools.
Three anti-overcrowding strategies. Leave out what the end users will not use · split into subsets across multiple pages · use the interactive tools.
Three ways to give context. Variation over time · comparison to an organisational goal · comparison internally across divisions or geographies, or externally across customers, segments or competitors.
Also consider. The environment (device, ambient lighting, display size and resolution, viewing distance, touch screen) and maintenance (who updates it, and what KPIs will matter next).
Explaining. Know your audience (needs, analytical comfort levels) · know your message (what helps the decision maker, empathising with data via the dot matrix chart and the big associated number) · Aristotle's rhetorical triangle (ethos credibility, pathos emotion, logos logic) · Freytag's pyramid (exposition, rising action, climax, falling action, denouement) · storyboarding first.
Honesty checklist. Zero baseline on any length encoding · no dual axis (use two charts or index to a common base) · full date range and a stated frequency · a rate not a total on a choropleth · no area or 3-D encoding of the number that matters · correct weighted aggregation for any ratio · state the sample size and the exclusions · adjust a long money series for inflation with a price index · say "moved together", not "caused".
Biases. Selection bias (the sample is unrepresentative) · survivor bias (only the cases that lasted are in the data) · Simpson's paradox (the aggregate relationship reverses within every subgroup; find it with a scatter chart coloured by subgroup).

Formula cards

Profit margin ratio

Profit Margin Ratio = DIVIDE ( [Total Profit], [Total Sales], 0 )

The measure the outline names. Always built from the two total measures rather than from a per-row column, so it is recomputed correctly at every level of every visual. Format as a percentage.

[Total Profit]: SUM(Sales[Profit]) under the current filters
[Total Sales]: SUM(Sales[Sales]) under the current filters
0: The alternate DIVIDE returns when sales are zero or blank

Total cost from sales and profit

Total Cost = [Total Sales] - [Total Profit] cost ratio + margin = 100%

Whenever a question gives two of sales, profit and cost and asks for the third. The identity that margin plus cost ratio makes 100 per cent is a free check on the arithmetic.

[Total Sales]: Revenue under the current filters
[Total Profit]: Profit under the current filters
Total Cost: What the sales cost to make and deliver

Iterate, then aggregate (SUMX)

SUMX ( Table, expr ) evaluates expr per row and adds; SUM(a) * SUM(b) is NOT SUMX(T, a*b)

Whenever the number to be added is computed per row from two or more columns: quantity times price, weight times rate. Using SUM on each column and multiplying introduces every cross pairing and is usually wrong by a large factor.

Table: The table to iterate, normally the fact table
expr: The per-row expression, evaluated in that row's row context
AVERAGEX: The same iteration, averaged instead of summed

Weighted average of ratios

overall ratio = SUM(numerators) / SUM(denominators) NOT the plain average of the ratios

Any time a question gives several percentages and asks for the overall figure. Recover each numerator first, add both columns, then divide. A plain average is only correct when every denominator is equal.

numerator: The additive top of the ratio, e.g. profit
denominator: The additive bottom, e.g. sales, which supplies the weight
weight: Each item's denominator as a share of the total denominator

Year-over-year growth

Sales LY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) YoY = DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] )

Needs a continuous Date table marked as the date table, and all date slicing done through it. Use DIVIDE, because a product launched this year has a prior-year base of zero and growth from zero is undefined.

'Date'[Date]: The date column of a gap-free date dimension
SAMEPERIODLASTYEAR: Shifts the current filter context back one year
YoY: Growth as a fraction; format as a percentage

Back to Visual Analytics