Power BI Exercise - Building a Dynamic Sales Target with a Disconnected Table

Let report readers test targets without changing or filtering the retail data.

In this guided exercise, you will create a selectable sales target, compare actual sales with the selected target, and display achievement status. The target table will remain disconnected from the star schema because it supplies user input rather than filtering transaction rows.

Learning goal: use GENERATESERIES and SELECTEDVALUE to build interactive scenario measures from a disconnected table.

Disconnected tableGENERATESERIESSELECTEDVALUEKPI

What is a disconnected table?

A disconnected table is intentionally not related to the fact or dimension tables. A slicer can filter the disconnected table, and a measure can read the selected value. Because no relationship exists, the selection does not directly remove rows from Sales.

A dashed gap represents the intentional absence of a model relationship.

Before you begin

Use the completed retail star-schema model. Confirm that [Total Sales] returns 21,788 when report filters are cleared.

Step 1: Create the Sales Target table

1. On the Modeling ribbon, select New table.
2. Enter the following DAX expression.

Sales Target =
GENERATESERIES (
    15000,
    30000,
    1000
)

The table contains a column named Value with 16 possible targets, beginning at 15,000 and ending at 30,000.

3. Select Sales Target[Value].
4. Rename the column to Target Amount.
5. Format it as currency or a whole number with a thousands separator.

Step 2: Keep the table disconnected

1. Open Model view.
2. Place Sales Target near the model without connecting it to another table.
3. Confirm that no relationship line leaves Sales Target.

Do not create a relationship. Target Amount is not a Product, Date, Region, Channel, or transaction key. It is a scenario value chosen by the report reader.

Step 3: Create the Selected Sales Target measure

Selected Sales Target =
SELECTEDVALUE (
    'Sales Target'[Target Amount],
    22000
)

SELECTEDVALUE returns the selected target when exactly one value is active. If no value or multiple values are selected, the alternate result of 22,000 is returned.

Step 4: Create Target Variance

Target Variance =
[Total Sales] - [Selected Sales Target]

A positive result means sales are above target. A negative result means sales are below target.

Step 5: Create Target Achievement Percentage

Target Achievement % =
DIVIDE (
    [Total Sales],
    [Selected Sales Target]
)

Format the measure as Percentage with two decimal places.

Step 6: Create Target Status

Target Status =
SWITCH (
    TRUE (),
    [Target Variance] >= 0, "Target achieved",
    "Below target"
)

This text measure turns the numeric comparison into a report-friendly result.

Step 7: Add the target slicer

1. Add a Slicer visual.
2. Add Sales Target[Target Amount].
3. Open the slicer's Selection settings.
4. Enable Single select.
5. Select 22,000.

Single select keeps the report interaction clear. The measure still includes a 22,000 fallback for cases where the slicer is cleared or unavailable.

Step 8: Build the KPI cards

Add five Card visuals:

Card	Measure	Expected at target 22,000
Actual Sales	[Total Sales]	21,788
Selected Target	[Selected Sales Target]	22,000
Variance	[Target Variance]	-212
Achievement	[Target Achievement %]	99.04%
Status	[Target Status]	Below target

Step 9: Apply conditional formatting

1. Select the Target Variance card.
2. Open the callout-value colour formatting and select the fx button.
3. Format by Rules using [Target Variance].
4. Use red when the value is below 0 and green when the value is 0 or above.
5. Apply the same logic to the Target Status card background or callout value.

Step 10: Test different targets

Selected target	Total Sales	Variance	Achievement	Status
20,000	21,788	1,788	108.94%	Target achieved
22,000	21,788	-212	99.04%	Below target
25,000	21,788	-3,212	87.15%	Below target
30,000	21,788	-8,212	72.63%	Below target

Step 11: Validate in DAX Query View

Run this query to test four target values without clicking the report slicer:

EVALUATE
SUMMARIZECOLUMNS (
    'Sales Target'[Target Amount],
    FILTER (
        'Sales Target',
        'Sales Target'[Target Amount] IN { 20000, 22000, 25000, 30000 }
    ),
    "Total Sales", [Total Sales],
    "Selected Target", [Selected Sales Target],
    "Variance", [Target Variance],
    "Achievement", [Target Achievement %],
    "Status", [Target Status]
)
ORDER BY 'Sales Target'[Target Amount]

The result should contain the four rows shown in the preceding control table.

Step 12: Test the target with business filters

1. Select target 15,000, then select Store in the Channel slicer.
2. Confirm Store Sales is 15,243.
3. Confirm Target Variance is 243.
4. Confirm Target Achievement is 101.62%.
5. Confirm Target Status is Target achieved.

The Channel slicer filters Sales through the star schema. The disconnected target slicer independently supplies 15,000 to the measures. The measures combine both pieces of context.

Optional: use Power BI's built-in numeric parameter

Power BI Desktop can create the table, measure, and slicer automatically:

1. On the Modeling ribbon, select New parameter, then Numeric range.
2. Enter a minimum of 15,000, maximum of 30,000, and increment of 1,000.
3. Select Add slicer to this page.

The manual method in this exercise makes the underlying DAX and disconnected-table pattern visible.

Knowledge check

1. Why does Sales Target have no relationship to Sales?
2. What does GENERATESERIES create?
3. When does SELECTEDVALUE return 22,000?
4. What does a negative Target Variance mean?
5. How can a Channel slicer and target slicer influence the same KPI measure differently?

Troubleshooting

Problem	What to check
Selected Target always shows 22,000.	Confirm the slicer uses Sales Target[Target Amount] and has one value selected.
The slicer contains Value instead of Target Amount.	Rename the generated Value column in Data or Model view.
Target selection filters transaction rows.	Delete any relationship connected to Sales Target.
Achievement appears as a decimal.	Format Target Achievement % as Percentage.
Conditional colour is reversed.	Use red below zero and green at zero or above.

Exercise complete

You have built an interactive target scenario without changing the retail data or adding an artificial relationship. The same disconnected-table pattern can support discounts, exchange rates, growth assumptions, thresholds, and other user-controlled business inputs.

Completion checklist

• Sales Target contains values from 15,000 through 30,000.
• The table has no model relationship.
• The slicer allows one selected target.
• All four target measures return the expected results.
• KPI colours respond correctly to positive and negative variance.
• The DAX query returns the four control scenarios.

References

• Microsoft Learn: GENERATESERIES function (DAX)
• Microsoft Learn: SELECTEDVALUE function (DAX)
• Microsoft Learn: Create and use parameters to visualize variables