Forum Discussion

o59393's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
2 years ago

Measure and Calculated column not giving the same value

Hi all

 

I have created a measure which basically is a Sumif:

 

 

 

The above translated to dax measure would be:

 

 

Production Allocated by Plant & Line = 

DIVIDE(

CALCULATE(
    SUMX(
        'SKU by line - Official',
        'SKU by line - Official'[Demand by Bottler Full]
    )
)
,
CALCULATE(
    SUMX(
        'SKU by line - Official','SKU by line - Official'[Demand by Bottler Full]),
        ALLEXCEPT(
            'SKU by line - Official',
            'SKU by line - Official'[Manufacturing plant of production],
            'SKU by line - Official'[Merged Product],
            'SKU by line - Official'[Date]
        )
    ),1
)

 

 

When I create a calculated column with the measure above, I get a complete a value of 340.26 in each row:

 

 

 

The calculated column goes in a table called "Query1", while the measure was calculated in "SKU by line" table. See relationship below:

 

 

How can I correct the calculated column in order to get the same values as the measure? (the percentages are the correct ones)

 

Thanks. 

Need Help
Show and Tell

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    HI o59393,

    In fact, measure and calculate column are work on the different levels.

    Measure expressions are calculated based on the current row context and it can be responded with dynamic filter effects.  When you use this expression in calculate column, the row context will be fix to static value and it does not respond with filter effects from external.

    I'd like to suggest you take a look at the following links about the difference between measure and calculated columns and row and filter contexts if helps:

    Calculated Columns and Measures in DAX - SQLBI

    Row Context and Filter Context in DAX - SQLBI

    Notice: the data level of power bi(from parent to child level)

    Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

    Regards,

    Xiaoxin Sheng