Cálculo de Gross Margin

[Lucas Denker]

Olá boa tarde! Venho pedir ajuda para resolver um problema que está dando quando eu tento calcular uma medida.
Inicialmente vou dar o meu contexto, depois vou explicar meu problema com o máximo de exemplos possíveis e então mostro o que tentei fazer e o erro que estou enfrentando.
Obs.: Não sei se na base de exemplo que mandarei aqui irá dar o mesmo erro que está dando no meu arquivo oficial da empresa, por isso vou tentar explicar detalhadamente o que está acontecendo.

Contexto.:
Inicialmente possuo 5 bases de dados em Excel, são elas:
dContratos - Base que contém a informação de todos os clientes que possuem contratos e as características destes contratos
dClientes - Base que contém as informações de todos os clientes e suas características
dProduto - Base que contém as informações de todos os produtos e suas caterísticas
fVolume - Base que contém as informações de venda dos produtos 
fGrossMargin - Base que contém as informações de cada filial e para cada produto quanto é ganho ou perdido em reais por unidade vendida

Um dos cálculos que realizo é o de Volume Bruto Adicional, que é um comparativo do quanto estava sendo vendido antes do contrato e quando foi vendido depois do contrato, isso para cada cliente. (As formulas serão disponibilizadas abaixo, bem como pbix e o arquivo de origem excel)
O que eu gostaria de fazer é calcular para cada filial e para cada produto qual o Gross Margin referente a cada volume adicional que foi feito, ou seja pegar o valor do Gross Margin da tabela fGrossMargin e multiplicar pelo volume adcional feito para cada produto e depois para cada filial.

Problema.:
O erro que estou enfrentando é que para algumas filiais ocorre a seguinte tela (vale lembrar que estou filtrando neste caso apenas uma filial por vez):


Formula Gross Margin por Volume Adicional:

Gross Margin Vol. Add Bruto ($) =

SUMX(

    VALUES('dRoteiro (ultimo)'[Filial]),

    SUMX(

        VALUES(dProduto[CHILD_DESC]),

        [Volume Bruto Adicional (#)] * [Gross Margin ($)]

    )

)

Formula Volume Adicional:

Volume Bruto Adicional (#) =

SUMX(

    VALUES(dContratos[CODSISTEMA2]),

    VAR _StartofContract = CALCULATE( MAX( dContratos[INICIO] ) )

    /* Calcula a data de início do contrato mais recente para cada cliente */

    VAR _EndofContract = CALCULATE( MAX( dContratos[FIM] ) )

    /* Calcula a data de fim do contrato mais recente para cada cliente */

    VAR _Hoje = TODAY()

    VAR _UltimaDatacomDados =

        CALCULATE(

            MAX( dCalendario[Inicio do Mês] ),

            FILTER(

                ALL( dCalendario[Date] ),

                [Volume (#)] <> 0

            )

        )

    /* Calcula a última data com dados para cada cliente */

    VAR _Periods =

        FILTER(

            ALL(dCalendario),

            dCalendario[Inicio do Mês] >= _StartofContract &&

            dCalendario[Inicio do Mês] <= _EndofContract

        )

    /* Cria uma tabela com todas as datas desde o começo do contrato até o final do contrato */

    VAR _DiferencaMeses =

        SWITCH(

            TRUE(),

            YEAR( _EndofContract) >= YEAR( TODAY() ),

            DATEDIFF( _StartofContract , _UltimaDatacomDados , MONTH ) + 1,

            DATEDIFF( _StartofContract , _EndofContract , MONTH )

        )

    /* Calcula a diferença entre as datas de ínicio e final de contrato, caso o período de contrato seja maior que a data de hoje, calcula até a data atual */

    VAR _MediaMensal =

        DIVIDE(

            CALCULATE( [Volume (#)], _Periods ),

            _DiferencaMeses

        )

    /* Calcula a média mensal de volume desde o ínicio do contrato até seu termino */

    RETURN

        IF ( NOT ISBLANK( [A0 (#)] ) , ( _MediaMensal - [A0 (#)] ) * _DiferencaMeses )

)

 

/* Medida que calcula a média mensal de volume após o contrato e subtrai da medida A0, calculando assim o volume bruto adicional por cliente */

 

dúvida_forum_bi.pbix base_dúvida.xlsx

[Alex Pereira]

Esse erro ocorre porque a medida DAX no Power BI está consumindo muitos recursos e excede os limites de capacidade do serviço. Para resolver o problema, você pode começar revisando a lógica da sua medida para garantir que não está realizando cálculos desnecessários ou repetitivos. Utilizar variáveis pode ajudar a melhorar o desempenho ao evitar cálculos repetidos. Além disso, verifique se você está aplicando filtros e agregações apropriados para reduzir o volume de dados processados.

Também é importante considerar a simplificação do modelo de dados, reduzindo a complexidade das tabelas e dos relacionamentos. O particionamento de tabelas grandes ou o uso de filtros pode ajudar a reduzir a quantidade de dados processados de uma só vez. Se você estiver usando Power BI Premium, ajustar a configuração de recursos pode ser útil. Além disso, utilizar tabelas de agregação pode pré-calcular dados e melhorar o desempenho dessa consulta.

[Edson Igari]

@Lucas Denker, não sei se está certo, mas aqui carregou normal. Sua máquina é boa???

[Alex Pereira]

13 horas atrás, Edson Igari disse:

@Lucas Denker, não sei se está certo, mas aqui carregou normal. Sua máquina é boa???

@Edson Igari, esse erro geralmente ocorre devido ao consumo de memória do Power BI. Pode ser que a conta que você está utilizando tenha uma configuração ou quantidade de recursos diferente da dele, o que faz com que funcione corretamente no seu ambiente, mas não no dele. Por exemplo, ele pode estar utilizando uma conta Pro, enquanto a sua é PPU (Premium Per User), que tem limites de memória mais altos.

[Edson Igari]

1 hora atrás, Alex Pereira disse:

@Edson Igari, esse erro geralmente ocorre devido ao consumo de memória do Power BI. Pode ser que a conta que você está utilizando tenha uma configuração ou quantidade de recursos diferente da dele, o que faz com que funcione corretamente no seu ambiente, mas não no dele. Por exemplo, ele pode estar utilizando uma conta Pro, enquanto a sua é PPU (Premium Per User), que tem limites de memória mais altos.

Minha conta é PRO Alex, mas testei também com uma conta free e funcionou do mesmo jeito. O modelo de dados dele tem 537 linhas em uma fato e 47 linhas na outra. Você acha que um modelo desse tamanho geraria tal problema???
As configurações da máquina utilizada também podem influenciar, como memória RAM, processador, tudo isso influencia, pois do contrário, você poderia instalar o power bi numa máquina com windows home e rodar. 

 

[Henrique Merola Faria]

Bom dia @Lucas Denker!
A base que você pode passar é muito pequena, não apresenta problema de falta de recursos,
aí dificulta um pouco.
(edit: ate na conta online free funciona)

Mas, já que nosso problema é recurso, vamos lá:
- Todos os campos Códigos e Id estão como Texto => pode mudar para Inteiro ? => Economizaria muito espaço no modelo
- O campo chave da tabela fGrossMargin é a Descrição do Produto => além de consumir muito espaço, piora o desempenho
  Esta é a maior tabela que existe no modelo, imagino que seja semelhante no modelo real.
  => poderia incluir o ID do Produto como chave e excluir a Descrição ? (usa da dProduto nos visuais)

Acredito que isso já daria uma boa "enxugada" no modelo e talvez resolva a questão do erro.

Depois vejo a questão das medidas em si, mas, já adianto que em tese, usar Medida dentro de SUMX torna a renderização
dos visuais mais lenta. Se puder evitar seria bom, mas  às vezes é inevitável.

 

Editado Quinta às 12:22 por Henrique Merola Faria
correção

[Lucas Denker]

5 horas atrás, Henrique Merola Faria disse:

Bom dia @Lucas Denker!
A base que você pode passar é muito pequena, não apresenta problema de falta de recursos,
aí dificulta um pouco.
(edit: ate na conta online free funciona)

Mas, já que nosso problema é recurso, vamos lá:
- Todos os campos Códigos e Id estão como Texto => pode mudar para Inteiro ? => Economizaria muito espaço no modelo
- O campo chave da tabela fGrossMargin é a Descrição do Produto => além de consumir muito espaço, piora o desempenho
  Esta é a maior tabela que existe no modelo, imagino que seja semelhante no modelo real.
  => poderia incluir o ID do Produto como chave e excluir a Descrição ? (usa da dProduto nos visuais)

Acredito que isso já daria uma boa "enxugada" no modelo e talvez resolva a questão do erro.

Depois vejo a questão das medidas em si, mas, já adianto que em tese, usar Medida dentro de SUMX torna a renderização
dos visuais mais lenta. Se puder evitar seria bom, mas  às vezes é inevitável.

 

Qual seria seu conselho para não usar medidas dentro do SUMX? Usar variáveis ao invés da medida ajuda no processamento?

[Henrique Merola Faria]

Usar direto o que seria o código da medida original mesmo, tipo Sumx( tabela, sum (tabela[margem]), 
ao invés de sumx(tabela,[Margem total])

[Lucas Denker]

33 minutos atrás, Henrique Merola Faria disse:

Usar direto o que seria o código da medida original mesmo, tipo Sumx( tabela, sum (tabela[margem]), 
ao invés de sumx(tabela,[Margem total])

Certo entendi. Vou levar essas dicas em consideração e testar pra ver o que ocorre. Eu não consegui replicar o erro acho que devido a base de dados também a minha original tem milhares de milhares de linhas e essa que mandei tem poquissimas.
Obrigado por todas as dicas pessoal!

[Henrique Merola Faria]

Legal, nos mantenha informado !!

[Alex Pereira]

Em 05/09/2024 em 09:14, Edson Igari disse:

Minha conta é PRO Alex, mas testei também com uma conta free e funcionou do mesmo jeito. O modelo de dados dele tem 537 linhas em uma fato e 47 linhas na outra. Você acha que um modelo desse tamanho geraria tal problema???
As configurações da máquina utilizada também podem influenciar, como memória RAM, processador, tudo isso influencia, pois do contrário, você poderia instalar o power bi numa máquina com windows home e rodar. 

 

Com a quantidade real de dados, a medida pode não funcionar tão bem, mas como o exemplo fornecido é apenas uma amostra, o volume de dados é significativamente menor, o que permite que a medida funcione no nosso ambiente. Nesse caso, há vários procedimentos que ele pode seguir para otimizar o desempenho. O ideal seria revisar a lógica da medida DAX e buscar formas de minimizar os cálculos, a fim de reduzir o tempo de processamento.