• Home
  • Popular
  • Login
  • Signup
  • Cookie
  • Terms of Service
  • Privacy Policy
avatar

Posted by Micro Bot


29 Nov, 2024

Updated at 14 Dec, 2024

Sell Through DAX formula

Hello everyone! I need some help with DAX.

 

Basically, I have to calculate SELL_THROUGH of the stores (CD_LOJA_SAP) and skus (CD_EAN) where [SELLOUT] AND [POSICAO_ESTOQUE] are not blank ou zero.

 

Here is an example, using CD_LOJA_SAP:

1. How it is at the moment:

ex1.png

2. How I want it to be:

ex2.png

 

As you can see, the measure works correctly for the rows in the table. However, the issue lies with the "Total" row. I need it to exclude stores 1 and 2.

 

My model:

ex3.png

 

DAX measures:

 

SELL_THROUGH =
VAR MaxData = [MAX_DT_ESTOQUE]
VAR Estoque = [POSICAO_ESTOQUE]

VAR Sellout = [SELLOUT]

VAR Estoque_Sellout = [POSICAO_ESTOQUE_SELLOUT]

VAR ST =
SWITCH(
    TRUE(),
    ISBLANK(Sellout) || ISBLANK(Estoque), BLANK(),
    DIVIDE(Sellout, Estoque_Sellout) > 1, 1,
    DIVIDE(Sellout, Estoque_Sellout)
)

RETURN
ST

MAX_DT_ESTOQUE =
VAR MaxData = CALCULATE(MAX(fEstoque[DT_ESTOQUE]), ALL(fEstoque),fEstoque[CHAVE_COLECAO_COMERCIAL] = SELECTEDVALUE('Coleções'[DS_COLECAO]))

RETURN
MaxData

POSICAO_ESTOQUE =
VAR MaxData = [MAX_DT_ESTOQUE]

VAR PosicaoEstoque =
CALCULATE(
    SUM(fEstoque[QT_ESTOQUE]),
    fEstoque[DT_ESTOQUE] = MaxData
)

RETURN
PosicaoEstoque

 

SELLOUT =
VAR MaxData = [MAX_DT_ESTOQUE]

RETURN
CALCULATE(
    SUM(fSales[VOLUME]),
    fSales[DT_PEDIDO] <= MaxData
)

 

POSICAO_ESTOQUE_SELLOUT = [SELLOUT] + [POSICAO_ESTOQUE]

 

Could anyone please help me? Thank you so much!