Rateio no Power Query [ distribuição proporcional ]

Conheça os dois tipos de rateios existentes, e aprenda a fazer a distribuição proporcional dos valores com Power Query, ai no seu Excel e Power BI.

E melhor, tudo ao toque do mouse, sem necessidade de comandos mirabolantes no Power Query.

O que é Rateio no Power Query?

Conceito Básico

O rateio, ou distribuição proporcional, é uma técnica usada para dividir um valor total entre várias partes com base em proporções específicas.

Imagine dividir a conta do restaurante entre amigos conforme o que cada um comeu.
Isso é um rateio!

Onde se usa rateio no meio corporativo?

Ele é como “arroz de festa”, ou seja, está em todos os lugares, e vou citar alguns casos para você se situar:

• Despesas de Alimentação: Geralmente a empresa que fornece refeições emite 1 nota, com o total das refeições, e lá dentro, tem o almoço (ou janta) de todo pessoal da produção, administrativo e demais áreas.
  Para lançar essa nota, geralmente é feito um rateio entre as áreas (departamentos | centros de custo) que consumiram;
• Energia Elétrica: A fatura de energia elétrica é única, e não tem um “relógio medidor” em cada departamento, mas, de alguma forma as empresas distribuem o custo dessa nota, de acordo com a capacidade instalada;
• Depreciação do prédio: Supondo que a empresa está toda em um prédio, a despesa mensal com a depreciação será alvo de uma apropriação, possivelmente por m² dos departamentos ali instalados.

Algo que pode acontecer, que é mais avançado, é o rateio “em ramos” (em cascata), onde um departamento recebe rateio de outro, e depois desse “novo total de gastos”, ele será rateado a outros.
Como mostro na imagem a seguir.

Essa forma de rateio, mais complexa, tem um nome técnico robusto que é “rateio recursivo”, e os programadores amam isso.
Justamente porque a solução depende de uma execução em loop.

Na empresa que fui Controller por 10 anos, nosso rateio tinha 350 níveis, pois algumas ferramentas PESADAS recebiam rateios de tornos, manutenções, e assim por diante.

Hoje aqui, vou te ajudar a dar o primeiro passo, e fazer o primeiro nível, ou seja, sem “recursividade”.

Passo a Passo para Fazer Rateio no Power Query

Preparando os Dados

Primeiro, precisamos de uma tabela com os valores que queremos distribuir.
Digamos que temos a seguinte tabela de vendas:

| Produto | Vendas  |
|---------|---------|
| A       | 1000    |
| B       | 2000    |
| C       | 3000    |

Calculando o Total

Vamos somar todas as vendas para obter o total:

let
    Fonte = TabelaVendas,
    TotalVendas = List.Sum(Fonte[Vendas])
in
    TotalVendas

Calculando a Proporção

Agora, precisamos calcular a proporção de cada venda em relação ao total.
Para isso você pode adicionar uma coluna personalizada com [Vendas] / TotalVendas que o comando gerado pelo Power Query será esse aqui:

let
    Fonte = TabelaVendas,
    TotalVendas = List.Sum(Fonte[Vendas]),
    AdicionaProporcao = Table.AddColumn(Fonte, "Proporcao", each [Vendas] / TotalVendas)
in
    AdicionaProporcao

Aplicando o Rateio

Finalmente, aplicamos o valor que queremos distribuir proporcionalmente.
Suponha que queremos distribuir um bônus de 6000 entre os produtos. Você pode criar mais uma coluna personalizada, agora com [Proporcao] * 6000, ficando assim:

let
    Fonte = ...,
    TotalVendas = List.Sum(Fonte[Vendas]),
    AdicionaProporcao = Table.AddColumn(Fonte, "Proporcao", each [Vendas] / TotalVendas),
    AdicionaBonus = Table.AddColumn(AdicionaProporcao, "Bonus", each [Proporcao] * 6000)
in
    AdicionaBonus

Nossa tabela final ficaria assim:

| Produto | Vendas | Proporcao | Bonus  |
|---------|--------|-----------|--------|
| A       | 1000   | 0.1667    | 1000   |
| B       | 2000   | 0.3333    | 2000   |
| C       | 3000   | 0.5000    | 3000   |

Mas esse é só o começo

Eu preparei uma aula em vídeo te mostrando como fazer esse passo a passo que apresentei acima.
A aula é rapidinha, e usamos predominantemente o mouse.

Essa aula dispõe de material de apoio, que está disponível para download no final desse artigo. Solicite!

Rateio em Ramos [cascata]

No inicio desse artigo falamos sobre o rateio em RAMOS, onde existe uma “re-subtotalização” dos valores, para distribuir para os demais departamentos dependentes.

É um calculo mais complexo, e que depende de conhecimento mais avançado da Linguagem M, que está por trás do Power Query.

Mas, a noticia boa é que na FORMAÇÃO POWER QUERY 2.0, você tem um módulo todinho falando desse assunto, desde a concepção do problema, até a elaboração do comando máster blaster.

Conclusão

Com o Power Query, você transforma horas de cálculos chatos em minutos de magia. O rateio fica simples, preciso e automático.

Agora, que tal aprofundar ainda mais seu conhecimento e dominar o Power Query de verdade?
Conheça a Formação Power Query 2.0 e transforme seus dados em resultados!

Forte Abraço