Rateio de despesas por Cliente x Data de Pagamento

[lfcarvalho]

Olá,

por favor, preciso de uma dica no Power Query (PwQ) de como eu rateio as despesas por cliente e pelas suas respectivas datas de pagamento.

Ratear um quantidade de despesas por cliente eu já consegui, meu problema está em fazer estes por mes de pagamento, ou seja, duas variáveis Cliente e DataPagamento.

No aquivo em anexo informo 2 tabelas, a dimensão contém o fator de multiplicação de cada cliente por mês de faturamento e a tabela fato contendo as despesas. Só exibo os faturamentos e despesas de jan a mar.

 

O resultado esperado é obter o rateio das despesas de janeiro pelo fator multiplicador (d_FatorFaturamentoCliente_ffc) de cada despesa de janeiro por cliente. Em fevereiro, obter os valores, também respectivos ao período de faturamento e despesa já que eles se modificam de mês a mês.

 

O meu problema está em utilizar a variável data, ao não conseguir separar conforme explicado acima, na mesclagem entre as tabelas o valores de distribuem por cliente ignorando o relacionamento entre as tabelas, ou seja, ao mesclar as tabelas, a despesa de janeiro acaba por ser rateada diversas vezes tantos quantos forem os clientes e os meses no registro da fato.

 

Desde já agradeço pela ajuda.

 

 

Simulacao Rateio por cliente e mes pagamento.pbix

[lfcarvalho]

Olá Giancarlo,

obrigado pela ajuda, mas infelizmente por DAX creio não ser possível porque preciso mesclar a Tabela Dimensão com a Fato para obter o codigo sacado e ffc e é na mesclagem que há a propagação de registros "desnecessários".

De qualquer forma eu consegui resolver também esta questão de gerar dados desnecessários na mesclagem a ponto de onerar o PBI. A solução chega ser ridícula a ponto de me fazer sentir como uma formiga tonta (falta de conhecimento técnico) no meio de uma floresta (vastidão de recursos do power query).

A solução: simples, lá no momento da mesclagem bastaria eu selecionar mais de uma condição, ou seja, além de mesclar SacadoRateio da tabela 1 com a tabela 2, eu também tinha que mesclar a DTPagamento da tabela 1 com a da tabela 2, sendo assim, a mesclagem teria duas condições.

OBS: Para fazer este tipo de mesclagem é obrigatório selecionar as colunas na ordem correta, ou seja, a primeira coluna e a segunda coluna da tabela 1 tem que ser na mesma ordem na tabela dois. Veja os prints abaixo, tanto do visual quanto do código.

 
 

 

 

[lfcarvalho]

Exemplo do que preciso solucionar via power query:

Cliente 1 = fator 0,08 em jan

Cliente 1 = fator 0,06 em fev

Cliente 1 = fator 0,1 em mar

Cliente 2 = fator 0,03 em jan

Cliente 2 = fator 0,05 em fev

Cliente 2 = fator 0,11 em mar

 

Fator Cliente 1 jan x valor despesas jan

Fator Cliente 1 fev x valor despesas fev

Fator Cliente 1 mar x valor despesas mar

Fator Cliente 2 jan x valor despesas jan

Fator Cliente 2 fev x valor despesas fev

Fator Cliente 2 mar x valor despesas mar

e assim por diante,  Cliente 3, Cliente 4, Cliente 5.... Cliente N

 

 

[lfcarvalho]

Olá, consegui fazer o que eu precisava, através de adição de coluna personalizada. 

1- Em cada tabela adicionei uma coluna nome do mês;

2- Fiz a mescla entre as duas tabelas e ignorei o fato de mesclar o que eu não queria, por exemplo, pegar o valor de uma despesa de janeiro e ratear ela em janeiro, fevereiro e março, quando só queria janeiro. Deixe acontecer a tal discrepância de regra de negócio.

3- Adicionei uma coluna personalizada e criei uma regra para cada mês de pagamento onde a coluna adicionada receberia o valor x fator multiplicação caso o mês na coluna da primeira tabela fosse igual ao mês da coluna da segunda tabela.

 

if [Nome do Mês]=[FatPorCliente.Nome do Mês] then [ValorDespesa.ValorLancamento]*[FatPorCliente.FFC]

else  0

... mas um detalhe me incomoda e gostaria muito de encontrar outra solução... O fato de mesclar pode gerar centenas e milhares de linhas desnecessárias e onerar  o pbi. Por exemplo, na minha tabela original tenho 748 linhas contendo o valor das despesas lançadas, ao mesclar com a tabela dimensão (fator multiplicador cliente) contendo somente 3 meses e 49 linhas, a mescla resultou em mais de 123mil linhas. Depois de aplicar a solução acima, incluir um filtro para retirar valores igual a zero, reduzi a tabela para 22.879, ou seja, esta é a quantidade que realmente é valida para o contexto.  

Se eu aplicar a mesma solução acima, imagine o quanto o pbi terá que manipular quando chegar dezembro deste e dos demais anos. Por isto, resolvi o problema por ora, consigo entregar para o negócio o cálculo necessário, mas creio que estou onerando o pbi. Imagino que tenha alguma forma mais inteligente de se fazer isto.

Caso alguém tenha uma ideia melhor e possa compartilhar, eu agradeço desde já. A solução tem que ser via power query porque preciso de tabela física para compor o DRE que montei.


Por enquanto a solução dada e que funciona é a mencionada acima.
 

[Giancarlo Solbiati]

Fala companheiro,

Situacao e exemplo complexo, foi complicado de entender, mas acho que entendi teu objetivo.

Admito que pensei na mesma solucao que voce, em utilizar colunas calculadas com formulas condicionais (IF/ELSE). Entretanto, como nao executei, nao teria ideia da variacao no volume de dados. De fato, creio que esse modelo seja insustentavel a longo prazo. O que iria sugerir e que, creio, que voce consiga atingir o mesmo resultado criando somente uma medida utilizando DAX, sem ter que necessariamente criar uma coluna calculada. O racional seria:

Measure =

 

VAR Data_Faturamento = MONTH(Data_Faturamento)

 

VAR Data_Despesa = MONTH(Data_Despesa)

 

VAR Valor_Despesa = F_Despesa[Valor]

 

VAR Valor_FFC = d_FatorFaturamentoCliente[FFC]

 

return

 

IF(Data_Despesa = Data_Faturamento, Valor_Despesa*Valor_FFC,0)

Com isto, voe criaria uma medidas e nao uma coluna calculada, evitando inflar o arquivo com dados fisicos. Nao consegui testar, pois meu PowerBI esta em uma versao diferente que esta gerando conflitos com o seu arquivo.

Espero que funcione ou que ao menos te de uma orientacao. Me diga se fizer sentido ou se funcionar!