Sell Through DAX measure

[Anelyse]

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!

[DataNinja777]

@Anelyse ,

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:

1. Filtering Valid Rows for Total:
• The FILTER function within SUMX filters only rows where [SELLOUT] and [POSICAO_ESTOQUE] are not blank or zero.
2. 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.
3. 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,