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:
| Amount | Price |
| 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.
Poderão experimentar o ficheiro e efetuar o download neste link.
PBI Portugal 