Totais por patamares de preço/quantidade

A comunidade do Power BI (PBI) é principalmente um repositório de questões, apesar de na mesma existirem espaço de trocas de ideias e publicações das mais diversas funcionalidades do PBI (exemplos: Blog e Data Stories Gallery), o principal motor da comunidade são as dúvidas sobre funcionalidades e opções de utilização. Nesse contexto existem várias questões que muitas vezes nos obrigam a pensar de forma criativa.

Umas das questões que surgiu nos últimos tempos na comunidade estava relacionada com a necessidade calcular o total do preço de uma encomenda baseada numa tabela de quantidades e preços.

As definições da tabela de preços eram as seguintes:

AmountPrice
100€1,00
300€2,00
50€3,00
500€4,00
8000€5,00
900€6,00
2700€7,00
4700€8,00
6000€9,00

Tal como podemos ver as quantidades não crescem de forma constante ou linear com o aumento do preço unitário.

A questão baseava-se em determinar o preço total da compra de 2.000 unidades tendo em conta que cada patamar tem preços e quantidades diferentes. A ordem deveria ser preenchida começando no valor mais baixo e ir sendo preenchida até atingir o total da encomenda.

O cálculo para as 2000 unidades seria algo similar ao apresentado abaixo:

100 stock for    1 € =   100 €

300 stock for    2 € =   600 €

50 stock for      3 € =   150 €

500 stock for    4 € = 2.000 €

1050 stock for 5 € = 5.250 €

Total =                       8.100 €

No PBI se efetuarmos cálculos com base em tabelas a ordenação é realizada automaticamente de A-Z, ou seja, neste caso se fosse efetuado o cálculo com base na soma cumulativa de todos as quantidade abaixo de 2000 teríamos o seguinte:

50 stock for      3 € =   150 €

100 stock for    1 € =   100 €

300 stock for    2 € =   600 €

500 stock for    4 € = 2.000 €

900 stock for    6 € = 5.400 €

150 stock for    7 € = 1.050 €

Total =                       9.300 €

Como podemos verificar o valor de 5 € está ausente da conta assim como vamos ter 1.050 unidades que vão ser valorizadas a um valor superior.

Neste contexto o primeiro passo é colocar uma coluna Índice (através do editor das quereis).

Nota: Nos dados apresentados poderíamos prescindir da coluna índice uma vez que os preços unitários estão ordenados de 1 a 9, no entanto utilizando a coluna de índice poderemos utilizar o mesmo método de cálculo para outras tabelas.

Teremos então que criar a seguinte medida:

Total Values (all categories) = 
//Tabela temporária para calcular os valores cumulativos até atingirmos a quantidade da ordem
VAR Temporary_Table =
ADDCOLUMNS (
ALL ( Price_Quantity_Table[Price] );
"Cumulatives"; CALCULATE ( SUM ( Price_Quantity_Table[Amount] ); Price_Quantity_Table[Price] <= EARLIER ( Price_Quantity_Table[Price] ) );
"Index_N"; LOOKUPVALUE ( Price_Quantity_Table[Index]; Price_Quantity_Table[Price]; Price_Quantity_Table[Price] );
"Quantity"; LOOKUPVALUE ( Price_Quantity_Table[Amount]; Price_Quantity_Table[Price]; Price_Quantity_Table[Price] )
)

//Quantidade selecionada da ordem baseada nos valores cumulativos
VAR Quantity_Selection =
CALCULATE (
MAX ( Price_Quantity_Table[Amount] );
FILTER ( Temporary_Table; [Cumulatives] <= Parameter[Parameter Value] )
)

//Coluna índice para a quantidade selecionada
VAR Index_Selection =
CALCULATE (
MAX ( Price_Quantity_Table[Index] );
FILTER ( Temporary_Table; [Cumulatives] <= Parameter[Parameter Value] )
)

//Primeira quantidade acima da quantidade desejada para ser utilizada para o preço da última tranche da ordem
VAR Quantity_Selection_Above =
CALCULATE (
MAX ( Price_Quantity_Table[Amount] );
FILTER ( Temporary_Table; [Index_N] = Index_Selection + 1 )
)

//Quantidade cumulativa para calcular a diferença entre a quantidade da ordem e o último preço
VAR Quantity_Cumulative =
CALCULATE (
SUMX ( Price_Quantity_Table; Price_Quantity_Table[Amount] );
FILTER ( Price_Quantity_Table; Price_Quantity_Table[Index] <= Index_Selection )
)

//Preço da última quantidade
VAR Price_Selection_Above =
CALCULATE (
MAX ( Price_Quantity_Table[Price] );
FILTER ( Temporary_Table; [Index_N] = Index_Selection + 1 )
)
RETURN
CALCULATE (
SUMX ( Price_Quantity_Table; Price_Quantity_Table[Amount] * Price_Quantity_Table[Price] );
FILTER ( Temporary_Table; [Cumulatives] <= Parameter[Parameter Value] )
) + ( ( Parameter[Parameter Value] - Quantity_Cumulative ) * Price_Selection_Above )

Como podemos verificar abaixo o cálculo das quantidades irá ser feito de acordo com a ordem das quantidades e os preços. Este método poderá ser utilizado por exemplo para valorização de ordens de acordo com o método FIFO (First In First Out) em que o preço unitário seja diferente, cálculo de quantidades mínimas com patamares de preços, entre outras.

Orders_quantiy.png

Poderão experimentar o ficheiro e efetuar o download neste link.

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão /  Alterar )

Google photo

Está a comentar usando a sua conta Google Terminar Sessão /  Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão /  Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão /  Alterar )

Connecting to %s

%d bloggers like this: