Joviano

Bem vindo ao

Blog do Jovi!

O blog do Jovi tem como objetivo ajudar você com suas dúvidas, além de expandir seu conhecimento através da informação.

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.

rateio recursivo com power query

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.

rateio no power query

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.

rateio de despesas em ramos com power query

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

Material de Apoio

Fala, FERA! Se você quiser acompanhar todos os exemplos que mostramos aqui, temos um material de apoio completo para você.  Preencha o formulário abaixo, que você receberá o link para realizar download no seu e-mail.

1 Comentário

  • Muito bom
    Estou iniciando em análise de dados e Power BI, adorei
    Muito obrigado!

    Resposta

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Receba conteúdos do Jovi!

Digite seu interesse abaixo:

Quer Aprender sobre Power Query?

Conheça nossa formação e tudo que você pode aprender com ela.

Mais conteúdos gratuitos para você!

OPA, vejo que você é novo por aqui!

Temos um presente de boas vindas para os amantes de Blog!

E ai FERA?!

Não vou atrapalhar tua leitura deste super artigo.

Só quero te dizer que por ser um visitante novo aqui no site, nosso sistema está programado para te enviar um cupom de desconto para a FORMAÇÃO POWER QUERY 2.0, que é um pacotão de cursos do JOVI, com mais de 300 horas de conteúdo.

Informe seus dados a seguir, que te enviaremos as instruções para resgate desse cupom exclusivo.

Após enviar, pode fechar essa janela, que seu presente já está em processamento.

A ementa possui centenas de aulas, e resumimos em um painel de PowerBI o qual enviaremos a você.

Preencha seu nome e email que em instantes receberá a ementa completa deste pacote de cursos.

Formato Aceito: [+ código do pais] [código de área] [número]