Tableau data blending: compare sales and targets without inflated totals

You have daily sales in one source and a target recorded once per month in another. You combine the tables, add both measures to a chart, and suddenly the company has a target several times larger than the original spreadsheet says. Nobody has raised management’s ambitions. You have repeated a value while combining data.

I start this problem by writing down what one row means in each table. Only then do I choose a way to combine them. Tableau data blending can bring results from separate sources into a worksheet, but it still requires a deliberate comparison grain. This article uses small synthetic tables. All amounts are illustrative and use the same unit, so the arithmetic stays easy to inspect.

Define the comparison before connecting sources

A sales record might represent an order line, a complete order, or a daily total. A target might be defined by month, region and channel. A date column shared by both sources does not establish that their observations are comparable. I want a sentence such as: “Compare monthly sales for a region with that region’s monthly target.”

That sentence gives me a minimum key: month including the year, plus region. If the target also distinguishes channels, I need to include channel or deliberately prepare a total target first. Otherwise, two perfectly plausible numbers may describe different parts of the business. The chart will happily place them next to each other without explaining the mismatch.

I also check the measure definitions. Net sales and a gross target need reconciliation. So do sales after returns and a target defined before returns. Here, I assume comparable definitions, one region and calendar months. Those assumptions let us focus on aggregation. In your own report, write them down rather than relying on everyone to remember what the dashboard was supposed to mean.

A tiny example exposes the join problem

The sales source contains these records:

The target source contains one row per month and region:

Suppose I physically join sales to targets on month and region. January’s target of 80 appears alongside each of the two January sales records. Summing that joined target column gives 160. The actual target remains 80. Sales of 40 and 60 total 100, making correct attainment 125%. Dividing sales by the duplicated target would instead produce 62.5%.

You can follow that error directly from the rows. A one-to-many match is technically valid, so there need not be a warning. The mistake is applying a sum to a value repeated by the join. With thousands of transactions, the inflation can be enormous. With just a few matches, it may look like a minor disagreement between systems and survive a superficial review.

What blending contributes

With blending, Tableau queries sources separately, aggregates their results, then presents them together in a worksheet. It does not create a combined transaction table. This distinction is described in Blend Your Data. The configuration belongs to the worksheet rather than becoming one universal model for every future analysis.

For this monthly comparison, I want January sales of 100 alongside a target of 80, followed by February sales of 90 alongside a target of 100. Sales need to be aggregated to month and region before comparison with the target. I must still understand which source determines the values that can appear in the view. Correct aggregation alone does not guarantee complete coverage.

Blending is useful in existing workbooks that need separate data sources and worksheet-specific links. I do not treat it as a mandatory solution whenever tables have different grains. When designing a model from scratch, consider relationships first. An older tutorial that only discusses blending would leave you with an incomplete picture of the available approaches and might encourage unnecessary workbook complexity.

Prepare a shared month key

In the sales source, I create a field called [Month]:

DATETRUNC('month', [Date])

Replace [Date] with your actual date field. DATETRUNC truncates a date to the specified part, as documented in Tableau’s date functions reference. Both January records now have the beginning of January 2026 as their month key. The target source already stores the first day of each month as a date.

Check both the type and the underlying value. The text “January 2026”, a date, and the number 202601 can all describe the same period to a human, but they need reconciliation as keys. Changing the display format does not change the stored value. Avoid linking solely on a month name, because January from different years would then become a candidate for the same match.

A business calendar introduces another decision. Targets defined by fiscal period require the corresponding fiscal key. Timestamped transactions also need an agreed time zone and rule for assigning dates. This example deliberately uses dates without times, so converting a timestamp cannot move a transaction across a month boundary. Your production model should make these choices explicit before anybody debates target attainment.

Build a control table first

Add sales and targets as two independent data sources using Data > New Data Source. Adding another connection within one source takes you into a different modeling approach. Start with an empty worksheet and place month and region from sales on Rows. Because sales supplies the first field used in the view, it becomes the primary source for that worksheet.

Switch to the target source and inspect the linking fields. If names differ, use Data > Edit Blend Relationships to map month and region deliberately. Check the active link icons in the secondary source’s Data pane, then add the aggregated target measure to the view. The source roles and workflow are explained in the blending instructions linked above. Be precise about which source each field belongs to.

At this stage, I want an ordinary table rather than a polished dashboard. Show month, region, sales and target. January should have 100 next to 80; February should have 90 next to 100. If those values do not agree, inspect the source records and linking fields. Additional calculated fields will only make the cause harder to isolate while the basic comparison is still wrong.

The primary source also defines what can be missing

March exists only in the target source. A view built from sales may not show it. This is why checking January and February is insufficient: I also need to decide whether the report must display planned months with no sales yet. That is a coverage requirement, separate from the arithmetic question of whether the visible months have correct totals.

If all planned periods should form the basis of the report, I can build a new worksheet starting with targets and bring in sales as the secondary source. That creates a different domain: target records now determine the available periods. If I need a complete calendar, I consider a period table and a model designed for that requirement. Switching the primary source does not magically create every possible category or period.

Missing sales also require interpretation. They might mean no transactions, a file that has not arrived, or a period that has not finished. I do not automatically replace every missing value with zero. Zero states something about business activity; a missing value may state something about data availability. The report needs enough context for the reader to tell those situations apart before drawing a performance conclusion.

Calculate attainment after validating both amounts

Once the amounts are correct, divide monthly sales by the monthly target. January gives 100 divided by 80, or 1.25, displayed as 125%. February gives 90 divided by 100, displayed as 90%. A zero or missing target requires a separate rule. Here, I leave attainment undefined and explain the reason rather than inventing a percentage that looks complete.

A calculation using a secondary-source measure needs aggregation. Tableau requires secondary measures to be aggregated in calculations. Instead of offering a supposedly universal expression containing an invented connection name, insert the target field from your own Data pane and inspect the source reference supplied by the editor. Validate the numerator and denominator separately before trusting their ratio in the final chart.

Now add day or product to the rows. A monthly target does not become a daily or product target because the worksheet contains more detail. It may appear alongside several marks, while retaining its monthly meaning. Daily attainment needs daily targets or an agreed allocation method. Dividing evenly by the number of days is itself a business assumption, especially when weekends and weekdays behave differently.

An asterisk and a missing match are different problems

An asterisk beside a secondary dimension can mean that multiple dimension values correspond to one mark. Imagine targets split by channel while the link uses only region and month. The amounts can be summed, but several channel names do not become one unambiguous label. Selecting an arbitrary label merely hides the mismatch between the dimension and the view’s level of detail.

First decide whether channel belongs in the analysis grain. If it does, you need matching fields and appropriate data in both sources. If it does not, describe the target as a total without attaching one channel’s label. Troubleshoot Data Blending also describes restrictions on non-additive aggregates such as COUNTD and MEDIAN. Conclusions drawn from summing amounts do not automatically transfer to those calculations.

A missing measure takes me down another path: active links, actual key values, date values and filters. “North” and “Północ” do not match merely because I understand both words. In a larger model, I prefer a region identifier and a controlled name dictionary. Trimming stray spaces can help, but it cannot resolve different meanings, different classifications or an incorrectly selected version of a planning file.

Choose relationships, joins or blending deliberately

Current Tableau relationships keep logical tables separate within a data source and preserve their individual levels of detail. Tableau recommends relationships as a first approach to combining tables in Relate Your Data. Daily sales and monthly targets can therefore also be candidates for a properly designed relationship-based model. Different grain alone does not force you to use blending.

A physical join is appropriate when I deliberately need a common row structure and understand the resulting multiplicity. Blending is an option when I need aggregated results from separate sources together in a particular worksheet. Available sources, constraints and the report’s intended use determine the choice. A useful explanation should identify those requirements rather than describe any mechanism as universally intelligent.

I also avoid promising better performance without measuring the actual workbook. Source size, queries and the view’s structure all matter. Establish a correct result and an understandable model first, then evaluate response time. If you are new to the tool, start with the Tableau introduction. For comparisons over time, continue with the MoM and YoY guide.

Check filters and planning versions

Suppose the January view is filtered to one sales channel while targets still cover all channels together. The numerator now describes part of the business; the denominator may still describe the whole region. You can calculate that ratio, but you cannot call it the channel’s target attainment without a channel target. Ask whether the missing breakdown exists before introducing an allocation assumption into the workbook.

Planning versions deserve the same attention. An original target, a revised target and a forecast may all appear as rows for the same month and region. They are alternative versions rather than three components to add together. Define which version the report compares with sales and ensure that only that version participates. A larger sum is no help if nobody can explain what it represents.

Check units and refresh coverage as well. A target expressed in thousands cannot be compared directly with sales in individual units. Ten days of sales against a full-month target may be a useful progress view, but it needs a clear coverage date. It should not silently become an assessment of a completed month when the underlying execution data are still partial.

I also keep separate control views for sales alone and targets alone. They let another analyst compare source totals with the blended result without understanding every calculation in the dashboard. Record the chosen linking fields and the expected target grain alongside them. If a later file adds product-level planning or another version, that person has a concrete definition to revisit before changing the report.

An exercise before handing over the report

Recreate the tiny tables above and prepare a monthly view. Then add another January sales record, change a region name in targets, and remove February sales. Before each change, write down the expected sales, target and visible periods. The exercise is about explaining behavior, rather than producing a chart that resembles an illustration or happens to show a plausible grand total.

Build a second view starting from targets. Explain why March appears and what missing sales should mean there. Next add day to the worksheet and decide whether summing the displayed target across all days would be valid. If you can trace every amount and every absence back to a definition, you understand the central difficulty of blending well enough to discuss its suitability for your report.

When handing over the workbook, leave a short definition of its grain: each output row represents month and region, targets have not been allocated to days, and the chosen primary source defines the period coverage. That note helps the next analyst understand which questions the dashboard can answer. It also gives them a clear reason to revisit the model when someone asks for a more detailed breakdown.