Sell Through DAX measure

Anonymous — Fri, 29 Nov 2024 18:19:44 GMT

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

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!

Re: Sell Through DAX measure

DataNinja777 — Sat, 30 Nov 2024 07:50:31 GMT

Anonymous ,

The issue with the "Total" row in your SELL_THROUGH calculation is due to how Power BI calculates totals in measures. The total is not a sum of the row-level values but rather a recalculation of the measure in the context of all rows.

To fix the issue and exclude stores that should not contribute to the total (e.g., stores 1 and 2 in your example), you need to explicitly filter out those stores where [SELLOUT] or [POSICAO_ESTOQUE] is blank or zero when calculating the total.

Here’s how you can adjust your SELL_THROUGH measure:

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) || Estoque = 0 || Sellout = 0, BLANK(), DIVIDE(Sellout, Estoque_Sellout) > 1, 1, DIVIDE(Sellout, Estoque_Sellout) ) VAR ValidRows = SUMX( FILTER( ADDCOLUMNS( VALUES(fEstoque[CD_LOJA_SAP]), "ValidRow", NOT(ISBLANK([POSICAO_ESTOQUE])) && NOT(ISBLANK([SELLOUT])) && [POSICAO_ESTOQUE] > 0 && [SELLOUT] > 0 ), [ValidRow] ), ST ) RETURN IF(HASONEVALUE(fEstoque[CD_LOJA_SAP]), ST, ValidRows)

Explanation of the Adjustments:

Filtering Valid Rows for Total:

The FILTER function within SUMX filters only rows where [SELLOUT] and [POSICAO_ESTOQUE] are not blank or zero.

Row-Level vs. Total Context:

The measure checks if the current context is row-level using HASONEVALUE(fEstoque[CD_LOJA_SAP]). If true, it calculates ST as usual.
For the total row, it calculates the ValidRows variable, summing up only the valid rows' ST values.

Excluding Invalid Rows:

Stores with [SELLOUT] or [POSICAO_ESTOQUE] blank or zero are excluded from the total calculation.

This approach ensures that the total reflects only the valid rows, excluding stores like 1 and 2 in your example.

Let me know if you need further clarifications or additional adjustments!

Best regards,

topic Sell Through DAX measure in DAX Commands and Tips

Sell Through DAX measure

Re: Sell Through DAX measure