# Calculation Groups

> Creates calculation groups for reusable DAX patterns like time intelligence and currency conversion. Use to replace repetitive measures with dynamic calculations.

- **Type:** Skill
- **Install:** `agentstack add skill-kpbray-power-bi-agent-skills-calculation-groups`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [kpbray](https://agentstack.voostack.com/s/kpbray)
- **Installs:** 0
- **Category:** [Agent Skills](https://agentstack.voostack.com/c/agent-skills)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [kpbray](https://github.com/kpbray)
- **Source:** https://github.com/kpbray/power-bi-agent-skills/tree/master/.github/skills/calculation-groups

## Install

```sh
agentstack add skill-kpbray-power-bi-agent-skills-calculation-groups
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# Calculation Groups Skill

This skill helps create calculation groups for reusable DAX patterns that can be applied dynamically to any measure.

## When to Use This Skill

- Replacing repetitive time intelligence measures (YTD, PY, YoY for every base measure)
- Creating currency conversion logic
- Building dynamic period comparisons
- Implementing conditional formatting patterns
- Creating calculation templates for multiple measures

## Benefits of Calculation Groups

| Without Calculation Groups | With Calculation Groups |
|---------------------------|------------------------|
| Sales YTD, Cost YTD, Profit YTD, Revenue YTD... | 1 base measure + Time Intelligence group |
| 4 base × 5 time calcs = 20 measures | 4 base + 5 calc items = 9 objects |
| Hard to maintain | Single point of change |

## Calculation Group Structure

### TMDL Syntax

```tmdl
calculationGroup 'Time Intelligence'
	precedence: 100
	lineageTag: 

	calculationItem 'Current' =
		SELECTEDMEASURE()
		ordinal: 0
		lineageTag: 

	calculationItem 'YTD' =
		CALCULATE(
		    SELECTEDMEASURE(),
		    DATESYTD(Date[Date])
		)
		ordinal: 1
		lineageTag: 
```

### Key Concepts

| Concept | Description |
|---------|-------------|
| `calculationGroup` | Container for calculation items |
| `calculationItem` | Individual calculation pattern |
| `SELECTEDMEASURE()` | Reference to whatever measure is being modified |
| `precedence` | Order when multiple groups applied |
| `ordinal` | Order of items in slicer/dropdown |

## Creating a Calculation Group

### Step 1: Create the Table

Calculation groups require a table with a single column:

```tmdl
table 'Time Calculation'
	lineageTag: 

	column 'Time Calculation'
		dataType: string
		isHidden: false
		summarizeBy: none
		sortByColumn: 'Time Calculation Order'
		lineageTag: 

	column 'Time Calculation Order'
		dataType: int64
		isHidden: true
		summarizeBy: none
		lineageTag: 

	calculationGroup 'Time Intelligence'
		precedence: 100
		lineageTag: 

		calculationItem 'Current' =
			SELECTEDMEASURE()
			ordinal: 0
			formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
			lineageTag: 

		calculationItem 'YTD' =
			CALCULATE(
			    SELECTEDMEASURE(),
			    DATESYTD(Date[Date])
			)
			ordinal: 1
			formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
			lineageTag: 
```

### Step 2: Configure Precedence

When multiple calculation groups apply to the same measure:

| Precedence | Effect |
|------------|--------|
| Higher number | Applied later (outer calculation) |
| Lower number | Applied first (inner calculation) |

Example:
- Currency (precedence: 50) - converts currency first
- Time Intelligence (precedence: 100) - then applies time calculation

## Time Intelligence Calculation Group

### Complete Example

```tmdl
table 'Time Calculation'
	lineageTag: a1b2c3d4-e5f6-7890-1111-000000000001

	column 'Time Calculation'
		dataType: string
		summarizeBy: none
		sortByColumn: 'Time Calculation Order'
		lineageTag: a1b2c3d4-e5f6-7890-1111-000000000002

	column 'Time Calculation Order'
		dataType: int64
		isHidden
		summarizeBy: none
		lineageTag: a1b2c3d4-e5f6-7890-1111-000000000003

	calculationGroup 'Time Intelligence'
		precedence: 100
		lineageTag: a1b2c3d4-e5f6-7890-1111-000000000004

		/// Current period value (no modification)
		calculationItem 'Current' =
			SELECTEDMEASURE()
			ordinal: 0
			formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
			lineageTag: a1b2c3d4-e5f6-7890-1111-000000000010

		/// Year-to-date calculation
		calculationItem 'YTD' =
			CALCULATE(
			    SELECTEDMEASURE(),
			    DATESYTD(Date[Date])
			)
			ordinal: 1
			formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
			lineageTag: a1b2c3d4-e5f6-7890-1111-000000000011

		/// Month-to-date calculation
		calculationItem 'MTD' =
			CALCULATE(
			    SELECTEDMEASURE(),
			    DATESMTD(Date[Date])
			)
			ordinal: 2
			formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
			lineageTag: a1b2c3d4-e5f6-7890-1111-000000000012

		/// Quarter-to-date calculation
		calculationItem 'QTD' =
			CALCULATE(
			    SELECTEDMEASURE(),
			    DATESQTD(Date[Date])
			)
			ordinal: 3
			formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
			lineageTag: a1b2c3d4-e5f6-7890-1111-000000000013

		/// Prior year value
		calculationItem 'PY' =
			CALCULATE(
			    SELECTEDMEASURE(),
			    SAMEPERIODLASTYEAR(Date[Date])
			)
			ordinal: 4
			formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
			lineageTag: a1b2c3d4-e5f6-7890-1111-000000000014

		/// Prior year YTD
		calculationItem 'PY YTD' =
			CALCULATE(
			    SELECTEDMEASURE(),
			    SAMEPERIODLASTYEAR(Date[Date]),
			    DATESYTD(Date[Date])
			)
			ordinal: 5
			formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
			lineageTag: a1b2c3d4-e5f6-7890-1111-000000000015

		/// Year-over-year change
		calculationItem 'YoY' =
			VAR CurrentValue = SELECTEDMEASURE()
			VAR PriorValue = CALCULATE(
			    SELECTEDMEASURE(),
			    SAMEPERIODLASTYEAR(Date[Date])
			)
			RETURN
			    CurrentValue - PriorValue
			ordinal: 6
			formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
			lineageTag: a1b2c3d4-e5f6-7890-1111-000000000016

		/// Year-over-year percentage change
		calculationItem 'YoY %' =
			VAR CurrentValue = SELECTEDMEASURE()
			VAR PriorValue = CALCULATE(
			    SELECTEDMEASURE(),
			    SAMEPERIODLASTYEAR(Date[Date])
			)
			RETURN
			    DIVIDE(CurrentValue - PriorValue, PriorValue)
			ordinal: 7
			formatStringDefinition: "0.0%;-0.0%;0.0%"
			lineageTag: a1b2c3d4-e5f6-7890-1111-000000000017
```

## Currency Conversion Group

```tmdl
table 'Currency'
	lineageTag: b2c3d4e5-f6a7-8901-2222-000000000001

	column 'Currency'
		dataType: string
		summarizeBy: none
		lineageTag: b2c3d4e5-f6a7-8901-2222-000000000002

	calculationGroup 'Currency Conversion'
		precedence: 50
		lineageTag: b2c3d4e5-f6a7-8901-2222-000000000003

		/// No conversion (base currency)
		calculationItem 'USD' =
			SELECTEDMEASURE()
			ordinal: 0
			formatStringDefinition: "$#,##0.00"
			lineageTag: b2c3d4e5-f6a7-8901-2222-000000000010

		/// Convert to EUR
		calculationItem 'EUR' =
			SELECTEDMEASURE() * 0.92
			ordinal: 1
			formatStringDefinition: "€#,##0.00"
			lineageTag: b2c3d4e5-f6a7-8901-2222-000000000011

		/// Convert to GBP
		calculationItem 'GBP' =
			SELECTEDMEASURE() * 0.79
			ordinal: 2
			formatStringDefinition: "£#,##0.00"
			lineageTag: b2c3d4e5-f6a7-8901-2222-000000000012

		/// Convert using rate table
		calculationItem 'Dynamic' =
			VAR SelectedCurrency = SELECTEDVALUE(CurrencySelection[Currency], "USD")
			VAR Rate = LOOKUPVALUE(
			    ExchangeRates[Rate],
			    ExchangeRates[Currency], SelectedCurrency
			)
			RETURN
			    SELECTEDMEASURE() * Rate
			ordinal: 3
			lineageTag: b2c3d4e5-f6a7-8901-2222-000000000013
```

## Format String Expressions

### Preserving Original Format

```tmdl
calculationItem 'Current' =
	SELECTEDMEASURE()
	formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
```

### Custom Format

```tmdl
calculationItem 'YoY %' =
	DIVIDE(CurrentValue - PriorValue, PriorValue)
	formatStringDefinition: "0.0%;-0.0%;0.0%"
```

### Conditional Format

```tmdl
calculationItem 'Variance' =
	[Current] - [Budget]
	formatStringDefinition:
		VAR Value = SELECTEDMEASURE() - [Budget]
		RETURN
		    IF(Value >= 0, "#,##0;(#,##0)", "#,##0;(#,##0)")
```

## Using Calculation Groups in Reports

### As Slicer

Add the calculation group column to a slicer to let users choose the calculation:

```json
{
  "visual": {
    "visualType": "slicer",
    "query": {
      "queryState": {
        "Values": {
          "projections": [{
            "field": {
              "Column": {
                "Expression": { "SourceRef": { "Entity": "Time Calculation" } },
                "Property": "Time Calculation"
              }
            }
          }]
        }
      }
    }
  }
}
```

### On Axis

Put calculation items on rows/columns of a matrix:

```json
{
  "queryState": {
    "Columns": {
      "projections": [{
        "field": {
          "Column": {
            "Expression": { "SourceRef": { "Entity": "Time Calculation" } },
            "Property": "Time Calculation"
          }
        }
      }]
    }
  }
}
```

### Default Selection

To default to "Current" when no item selected, use `ISSELECTEDMEASURE()`:

```tmdl
calculationItem 'Current' =
	SELECTEDMEASURE()
	ordinal: 0
```

With ordinal 0, "Current" appears first and is often the default.

## Advanced Patterns

### Period Over Period Comparison

```tmdl
calculationItem 'vs Prior Period' =
	VAR CurrentPeriodValue = SELECTEDMEASURE()
	VAR PriorPeriodValue =
	    CALCULATE(
	        SELECTEDMEASURE(),
	        DATEADD(Date[Date], -1, MONTH)
	    )
	RETURN
	    CurrentPeriodValue - PriorPeriodValue
	formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
```

### Rolling Calculations

```tmdl
calculationItem 'Rolling 3M Avg' =
	AVERAGEX(
	    DATESINPERIOD(Date[Date], MAX(Date[Date]), -3, MONTH),
	    CALCULATE(SELECTEDMEASURE())
	)
	formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
```

### Cumulative Total

```tmdl
calculationItem 'Cumulative' =
	CALCULATE(
	    SELECTEDMEASURE(),
	    FILTER(
	        ALL(Date),
	        Date[Date] <= MAX(Date[Date])
	    )
	)
	formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
```

### Percentage of Total

```tmdl
calculationItem '% of Total' =
	DIVIDE(
	    SELECTEDMEASURE(),
	    CALCULATE(SELECTEDMEASURE(), REMOVEFILTERS())
	)
	formatStringDefinition: "0.0%"
```

### Moving Annual Total

```tmdl
calculationItem 'MAT' =
	CALCULATE(
	    SELECTEDMEASURE(),
	    DATESINPERIOD(Date[Date], MAX(Date[Date]), -12, MONTH)
	)
	formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
```

## Precedence Examples

### Time + Currency

```tmdl
/// Apply currency first (lower precedence)
calculationGroup 'Currency'
	precedence: 50

/// Then apply time intelligence (higher precedence)
calculationGroup 'Time Intelligence'
	precedence: 100
```

Result: Currency conversion happens first, then time calculation applies.

### Multiple Time Comparisons

If you have both "Time Period" and "Time Comparison":

```tmdl
/// Base time period selection
calculationGroup 'Time Period'
	precedence: 100

/// Comparison calculations
calculationGroup 'Time Comparison'
	precedence: 200
```

## Boundaries and Constraints

### DO
- Always include a "Current" or "Actual" item for base value
- Use `SELECTEDMEASUREFORMATSTRING()` to preserve formatting
- Set appropriate ordinal for logical ordering
- Use meaningful names for items
- Add descriptions with `///` comments
- Test with multiple base measures

### DO NOT
- Don't create too many calculation items (max 10-15)
- Don't apply to non-additive measures without testing
- Avoid complex logic that varies by measure type
- Don't forget format string expressions
- Never use calculation groups for one-off calculations

### Measures That May Not Work

Some measures don't work well with calculation groups:

| Measure Type | Issue |
|--------------|-------|
| Distinct count | Time intelligence may not apply correctly |
| Average | YTD average behavior may be unexpected |
| Ratios | Results may be meaningless |
| Semi-additive | Special handling needed |

## Workflow Integration

After creating calculation groups:
1. **Add to report** - Use the `report-visuals` skill for slicers/visuals
2. **Test thoroughly** - Verify with different base measures
3. **Document** - Explain the calculation group purpose
4. **Validate** - Use the `best-practices` skill to check quality

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [kpbray](https://github.com/kpbray)
- **Source:** [kpbray/power-bi-agent-skills](https://github.com/kpbray/power-bi-agent-skills)
- **License:** MIT

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-kpbray-power-bi-agent-skills-calculation-groups
- Seller: https://agentstack.voostack.com/s/kpbray
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.