Creating Visual Calculations with DAX
A sales director asks for a chart that shows monthly sales and a running total. The running total is needed only on that chart. It doesn’t need to appear in other reports, and nobody has asked for it as a centrally managed business metric. You could still create a measure in the semantic model, but should you? What evidence would show that your choice fits the requirement rather than merely producing plausible numbers?
That decision is part of the PL-300 skill to create visual calculations by using DAX. The important word is visual. A visual calculation is stored on an individual visual and evaluates the aggregated data available to that visual. It doesn’t become a reusable measure in the semantic model. This makes it a strong candidate for calculations such as a running sum, moving average, or period comparison when the result is genuinely local to one visual.
Start by separating the requested result from the method used to produce it. The director wants monthly sales beside a cumulative value. That’s the business outcome.
The calculation needs to follow the monthly values already represented in one visual, and there’s no requirement for other report objects to reuse it. Those details point toward a report-level visual calculation. The mere fact that DAX is involved doesn’t mean the logic must become a model measure.
Suppose the visual contains twelve monthly sales totals. Each total is already an aggregation of the sales records contributing to that month. The visual calculation operates over those aggregated values.
For a running sum, it can accumulate the first displayed monthly total, then the first two, then the first three, continuing across the data available to the visual. It isn’t being described here as a new column added to every underlying sales transaction. It’s calculating over the summarized result represented by the visual.
That distinction tells you why the Power BI layer matters. Power Query is used for refresh-time transformation of data before it becomes report output. The semantic model holds structures and calculations intended to support analysis across the model. A model measure can supply reusable DAX logic.
A visual calculation, by contrast, belongs to the visual and works over that visual’s aggregated data. These aren’t interchangeable locations for the same decision. Each location gives the calculation a different role and lifecycle.
Now imagine that operations asks for a moving average on a second visual, while finance wants the same definition on several report pages. The requirement is no longer narrowly local. If those consumers need one reusable or centrally governed definition, a model measure is the better choice. If the moving average is a presentation-specific calculation that belongs only to one visual, a visual calculation may fit.
The decision rule is simple but important. Use a visual calculation for logic that’s truly local to a visual. Use a model measure when the logic must be reused or governed centrally.
This also corrects a tempting misconception. If Power BI exposes a feature, that doesn’t make the feature appropriate for every report. An available visual calculation isn’t automatically better than a measure, and a familiar model measure isn’t automatically better than a visual calculation. The analyst has to ask where the logic belongs, what data it evaluates, who needs to reuse it, and how its correctness will be maintained.
Return to the monthly sales chart. Assume it displays monthly sales totals of ten thousand, fifteen thousand, and twelve thousand for the first three months. A running sum over those visible aggregates should produce ten thousand for the first month, twenty-five thousand for the second, and thirty-seven thousand for the third.
Those expected values give you something concrete to inspect. Seeing a calculation appear in the visual isn’t enough. You need to compare its output with a result you can independently reason through.
The phrase already-aggregated data deserves attention. The visual first has data available to it in an aggregated form, such as a sales total for each month. The visual calculation then works over that result.
This is different from assuming that the calculation has become a general-purpose object that any other visual can request. Its storage location and evaluation input are both part of its identity. It’s attached to the visual, and its calculation is based on the aggregated data available there.
Filter context also matters to your interpretation. Filter context is the set of filters affecting the data evaluated for a report visual or DAX expression. If the report is limited to one region, product group, or year, the aggregated values available to the visual may represent that selection rather than the entire dataset. The running sum or moving average must therefore be checked against the values presented under the filter condition you’re testing. A correct result for all regions doesn’t prove that the result remains meaningful under every consumer selection.
Suppose the normal view contains sales for all regions and the running sum looks reasonable. A user selects the North region, and the cumulative values no longer match what the analyst expected. Don’t begin by changing an unrelated Power Query transformation or redesigning the entire model. First reproduce the exact condition. Record the selected region, the monthly base values shown by the visual, the cumulative values actually produced, and the values that should result from those displayed aggregates.
That comparison helps isolate the layer responsible for the symptom. If the monthly sales values themselves are wrong, the issue may lie before the visual calculation, and the local running sum could simply be accumulating incorrect inputs. If the monthly values are correct but the cumulative output is wrong, attention should stay on the DAX result and the context in which the visual calculation evaluates. The calculation’s presence proves only that it was configured. It doesn’t prove that the logic or interpretation is correct.
After a correction, retest the ordinary view and the filtered view. The original all-region case confirms that you haven’t broken the behavior that appeared to work before. The North-region case confirms that you’ve addressed the reported failure. This two-part check matters because a change that fixes one condition can still leave a different condition incorrect. Observable output, not a successful edit, is the evidence that the requirement has been met.
A moving average creates a similar verification need. Imagine a visual that presents monthly sales and a smoothed result intended to help a manager see the broader direction. The label moving average sounds familiar, but familiarity doesn’t define the business requirement. You still need to know which aggregated values the result is meant to consider and then compare the visible output with a small set of values you can calculate independently. If the report is filtered, repeat the comparison in that state rather than assuming the unfiltered check covers it.
Period comparisons require the same discipline. A request to compare one period with another can sound like a generic model requirement, but the location still depends on intended use. If the comparison exists only to support one visual’s presentation, local logic may be appropriate.
If several visuals, pages, or consumers require the same governed comparison, a model measure better matches the reuse requirement. The calculation type alone doesn’t make the decision. Its required reach across the solution does.
This affects the person consuming the report. A director doesn’t see the storage location of the calculation. The director sees values, labels, filters, and the conclusions those values appear to support.
If a local calculation is mistaken for a governed metric, two visuals may be treated as though they share one definition when they don’t. If a reusable model measure is created for logic that was only a temporary visual need, the model may take on a business definition that nobody actually requested. Matching the layer to the requirement keeps the technical design aligned with the meaning presented to the consumer.
Consider two report pages maintained by different analysts. One page needs a running sales total for a particular chart. The other page may eventually need a cumulative value too, but its owner hasn’t established that the definition should be identical. Creating a local visual calculation on the first chart satisfies the current local requirement without claiming that a centrally governed cumulative metric now exists. If the organization later decides that both pages must use one shared definition, that’s evidence for moving the logic into a reusable model measure.
The reverse mistake is also possible. An analyst may create a visual calculation because it’s quick to place near the displayed result, then discover that several other visuals need the same logic. Copying the idea from visual to visual doesn’t turn those local calculations into one governed object. The requirement has changed from local presentation logic to reusable model logic. At that point, preserving the calculation as a model measure is the more appropriate design because reuse and central governance now matter.
Good validation begins before you inspect the output. Write down what should happen in ordinary business language. For the sales chart, that might be: show monthly sales, then show a cumulative value that increases by each month’s displayed sales total. Next, identify the relevant Power BI layer.
Because the cumulative value is requested only for this visual, use a visual calculation. Then choose a few aggregated values whose expected cumulative results are easy to establish. Only after defining those expectations should you decide whether the observed output is correct.
This prevents a common testing error. Large report values often look plausible even when they’re wrong. A cumulative result of several million may not attract attention if the underlying business already handles millions in sales.
A controlled test using three or four known aggregated values makes an error much easier to recognize. You’re not proving correctness by admiring a smooth line or steadily increasing total. You’re checking whether each result follows from the aggregated inputs available to the visual.
You should also test the way the intended consumer uses the report. If the consumer normally filters by region, validate at least one region. If a period has no contribution to the displayed total, include that condition in the expected-versus-actual comparison when it’s relevant to the report.
The accepted result must make sense in the context where the calculation is consumed. Configuration success is only an implementation event. Consumer-facing behavior is the outcome that needs verification.
Keep the troubleshooting path focused. First identify the symptom. Then reproduce the filter or interaction state in which it occurs. Inspect the base aggregated values available to the visual.
Compare the visual calculation’s output with the expected DAX result. Correct the setting or logic that directly governs the behavior, and retest both the ordinary and failing conditions. Randomly editing transformations, relationships, other visuals, or service settings can make the report harder to understand without addressing the local calculation at all.
There’s still an architectural question even when every tested number is correct. Ask whether the calculation remains local by design. A running sum can be numerically correct on one chart while still being the wrong solution if the business expected the same governed result on ten pages.
Conversely, a model measure can return correct values while adding unnecessary reusable logic for a one-off visual presentation. Numerical correctness and design appropriateness are related, but they aren’t identical. You need evidence for both.
For numerical correctness, inspect the base values, the expected result, and the actual visual output under controlled conditions. For design appropriateness, return to the requirement. Is the logic attached to one visual’s presentation, or is it a shared business calculation?
Does another report object need to call the same definition? Must one team maintain that definition centrally? Those answers tell you whether local storage on the visual is a benefit or a limitation.
This is why memorizing an interface path is weak preparation for an applied decision. An interface can tell you where an option appears, but it can’t decide whether the business needs local visual logic or a reusable semantic-model measure. It also can’t tell you whether a result is correct merely because the formula was accepted. You need to understand the mechanism, predict the output, and observe what the consumer receives.
The sales director’s original request can now be evaluated cleanly. Monthly sales are already aggregated in the chart. The running total is needed only there, so a visual calculation fits the stated scope. The analyst checks a few known monthly totals, verifies the cumulative results, applies a relevant filter, and repeats the comparison. If the business later asks for the same governed running total elsewhere, the design should be reconsidered rather than treating the first local choice as permanent.
That pattern carries into more complicated Power BI work. Restate the outcome, identify the layer that owns the behavior, apply the mechanism documented for that layer, and validate the result where users experience it. For visual calculations, that means remembering both halves of the feature. They operate over the aggregated data available to a visual, and they remain stored on that visual rather than becoming reusable model measures.
A visual calculation is more than DAX placed near a chart. Its location explains its purpose. It’s stored on the visual, evaluates the aggregated data available there, and works best when the logic is genuinely local. When a calculation must be reused or governed centrally, a model measure is the better architectural choice.
Whichever layer you choose, don’t accept a plausible result as proof. Establish the expected output from known aggregated values, reproduce relevant filter conditions, and compare what the consumer sees with what the calculation should produce. That combination of layer selection, mechanism, and observable verification turns a working configuration into a defensible Power BI solution.
Explain why a visual calculation belongs at the visual layer rather than in the semantic model. Which change in visual structure or filter state would expose that scope? The episode reference preserves the non-narrated details needed for practice and later recall. This lesson was produced by Bare Metal Cyber dot com.