Sell Through DAX measure

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:

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,