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:
2. How I want it to be:
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:
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!
Sell Through DAX formulaYou must login to comment