Capture Ratio

[Isadora Lupchinski]

Bom dia, membros da comunidade

Preciso de um help com DAX. Não é um problema tão trivial, por isso vou contextualizar e detalhar o mesmo.

Tenho um base de dados (fictícia; pbix em anexo) em que tenho a venda de algumas máquinas, e passado um certo tempo, sabemos que essas precisam de peças de reposição e acessórios, que são categorizadas por ‘Spare Parts’, ‘Accessories’ e ‘Diamant Tools’. Na base de dados tenho os campos com as ‘expectativas de vida’ tanto das máquinas, quanto dessas peças. Meu objetivo é calcular o ‘capture ratio’ para cada uma dessas categorias. Em geral, o cálculo é feito pelo rácio entre o consumo dos últimos 12 meses dessas peças a dividir pelas máquinas ativas.

Primeiramente, vou explicar a base de dados.

Precisei trazer as informações de 2 bases diferentes:

1)      uma base de dados que tinham os Diamant Tools e suas máquinas

2)      e outra que tinha os Spare Parts, Acessories e suas máquinas

Por isso temos as colunas 7 (SparePartType) e 10 (DiamantType). Foi necessário intercalar consultas no Power Query.

Legenda das variáveis:

1)      Categoria do produto

2)      Código de subgrupo

3)      Data da fatura

4)      Quantidade vendida

5)      Dados que vieram da Tabela SpareParts. Se for ‘(vazio)’ teremos os dados da tabela ‘Diamant Tools’. Irá notar que as colunas 8, 9 e 10 passam a apresentar informações, uma vez que essas bases de dados foram intercaladas no PQ

6)      Spare Part Lifecycle: Expectativa de vida das SpareParts em anos

7)      Tipo de Spare Parts

😎 Nome do grupo Diamant Tools

9)      Diamant Lifecycle: Expectativa de vida dos Diamant Tools

10)   Spare Part End Life: é o cálculo da data da fatura mais a expectativa de vida das Spare Parts

11)   Diamant Tools End Life: é o cálculo da data da fatura mais a expectativa de vida das Diamant Tools

12)   Country

13)   Net Sales Curr: valor da venda

Com relação aos cálculos, pretendo calcular 3 coisas:

1)      Capture Ratio Diamant Tools = consumo dos últimos 12 meses de DT/ máquinas ativas

2)      Capture Ratio Spare Parts = consumo dos últimos 12 meses de SP/ máquinas ativas desconsiderando os 6 meses mais recentes

3)      Capture Ratio Accessories= consumo dos últimos 12 meses Accessories/ máquinas ativas

 

Além disso, pretendo colocar cada um desses cálculos em matrizes, em que nas colunas, quero o tempo (ano e mês) e nas linhas ‘Product Subgroup’.

Como pensei em calcular o numerador das Diamant Tools:

Sales DT =

CALCULATE(

    SUM(Consolidada[NetSalesCurr]),

    Consolidada[DiamantType] = "Diamant Tools"

)

 

Consumo 12 meses DT =

CALCULATE(

    [Sales DT],

    DATESINPERIOD('dCalendário'[Date], MAX('dCalendário'[Date]), -12, MONTH)

)

 

Cálculo do denominador:

Sales Machine DT =

CALCULATE(

    SUM(Consolidada[NetSalesCurr]),

    Consolidada[DiamantType] = "Machine"

)

 

Máquinas Ativas DT =

CALCULATE(

    [Sales Machine DT],

    FILTER(

        ALLEXCEPT(Consolidada, Consolidada[ProductSubGroup]),

        Consolidada[InvoiceDate] <= MAX(Consolidada[InvoiceDate]) &&

        Consolidada[DiamantToolsEndLife] > MAX(Consolidada[InvoiceDate])

    )

)

 

Quando coloco em um matriz simples, somente com os anos nas linhas e os cálculos em valores, parece-me bem:

Porém, quando tento extratificar as informações por subgrupos, fica estranho:

 

Já agora sobre as Spare Parts, existem algumas peculiaridades. Vamos à explicação sobre a área de negócio.

 

Em relação ao Diamond Tools, é suposto nós fazermos vendas logo a seguir á venda da máquina, pois as ferramentas diamantadas são um consumível recorrente.

Mas em relação às Spare Parts, enquanto a máquina é nova, nós não vamos fazer reparações na mesma, pelo que estimamos este período de “carência” de 6 meses.

Ou seja, se analisarmos este rácio no mês de Junho/2021, vamos considerar vendas de Spare Parts de Julho/2020 a Junho/2021 e vendas de máquinas de Julho/2018 a Dezembro/2020 (no caso das máquinas pequenas em que consideramos 2,5 anos). Ou seja, não consideramos a venda de máquinas dos últimos 6 meses (porque em teoria não foram vendidas SP para essas máquinas).

Como desenvolver esses cálculos? Alguma ideia?

 

Lifecycle_machine.pbix

[Frank Figueredo]

@Isadora Lupchinski conseguiu resolver?

[Isadora Lupchinski]

Consegui

[Frank Figueredo]

Que bom que conseguiu resolver @Isadora Lupchinski, se puder compartilhar mais detalhes sobre como resolver, e a solução, seria muito bom, pois caso alguém esteja passando por algo similar já terá algum norte.