Medidas calculadas

Uma medida calculada é uma coluna cujo valor vem de uma fórmula sobre outras medidas: uma margem, um rácio, uma variação, uma percentagem do total.

Porque não no Excel

A tentação é óbvia: acrescentar uma coluna margem à folha e importá-la. Não faça isso.

Uma folha com fórmulas envelhece mal. Ninguém sabe se um número foi recalculado depois da última alteração, e dois dashboards a somar a mesma coisa acabam com totais diferentes por causa de uma fórmula que ficou por arrastar.

Calcular na consulta mantém uma só verdade: os dados que entram são base (factos: valor, custo, quantidade) e as contas fazem-se onde são usadas.

Como se escreve

Uma medida calculada tem duas partes.

Operandos — cada um é um nome, uma tabela, um campo é um agregado. Escolhem-se em seletores, não se escrevem.

Receita  =  SUM(Vendas.valor)
Custo    =  SUM(Vendas.custo)

Formula — texto que combina os operandos.

(Receita - Custo) / Receita * 100

O resultado é uma coluna de output como qualquer outra, com o seu nome de saída, utilizável num indicador, num gráfico ou numa tabela.

O que a fórmula aceita

Permitido	Exemplo
Nomes de operandos declarados	Receita
Números	100, 0.5
Operadores	+, -, *, /
Parenteses	(A - B) / B
Sinal negativo	-A

Mais nada. Não há funções, não há texto, não há comparações. A fórmula é convertida numa árvore de operações e só os nós dessa árvore geram cálculo — não há caminho por onde uma expressão arbitrária chegue a base de dados.

Nomes de operandos começam por letra e usam letras, dígitos e _.

Divisão: duas armadilhas já tratadas

Denominador zero. Toda a divisão sai protegida: um rácio impossível devolve vazio em vez de rebentar a consulta inteira. Acontece a serio — o mês em que ainda não há receita, a loja que abriu ontem.

Divisão inteira. Duas colunas de números inteiros dariam um resultado truncado: 7 / 2 seria 3, e um "35%" apareceria como 0 sem erro nenhum. O numerador é convertido antes de dividir, e o resultado é decimal.

Exemplos úteis

Margem bruta em percentagem

Receita = SUM(valor)
Custo   = SUM(custo)

(Receita - Custo) / Receita * 100

Ticket medio

Vendas     = SUM(valor)
Encomendas = COUNT(número)

Vendas / Encomendas

Desconto medio ponderado pelo valor — diferente da média simples dos descontos, que trata uma venda de 10 euros como uma de 10 000:

DescontoTotal = SUM(desconto)
Linhas        = COUNT(número)

DescontoTotal / Linhas * 100

Preco medio por unidade

Valor    = SUM(valor)
Unidades = SUM(quantidade)

Valor / Unidades

Percentagem de cumprimento de meta

Realizado = SUM(valor)
Meta      = SUM(metaValor)

Realizado / Meta * 100

Agregado dos operandos

Cada operando tem o seu agregado — normalmente SUM, mas pode ser qualquer um. É possível ainda usar um operando sem agregado, que lê o valor da linha; nesse caso a coluna deixa de ser uma medida e passa a comportar-se como dimensão, o que raramente é o que se quer.

A regra prática: mantenha todos os operandos agregados.

Mensagens de erro

As mensagens são escritas para quem escreveu a fórmula:

Mensagem	Causa
A fórmula acaba a meio — falta um valor.	Um operador sem nada a seguir: A -
Falta fechar um parêntesis.	(A - B / B
Há texto a mais no fim da fórmula.	A / B )
O carácter "x" não é permitido numa fórmula.	Um simbolo fora do conjunto permitido, como % ou ,

Uma percentagem escreve-se * 100, não %. Decimais escrevem-se com ponto: 0.5, não 0,5.

Onde entram no agrupamento

Uma medida calculada é uma medida: não entra no agrupamento, e a sua presença faz com que as dimensões entrem. Uma consulta com categoria (dimensão) e uma margem calculada devolve uma linha por categoria, com a margem daquela categoria.

Isto é mais correcto do que parece: a margem de cada categoria é calculada a partir dos totais dessa categoria, e não como média de margens por linha.