Same expression for calculated column and measure but different result

[Anonymous]

I have basically the same(I think they are the same, I could be wrong)expression for calculated colum and measure, yet it come out with complete different results:

Calculated column:

Adj Bse % = IF(BTA[D_EligibleBase_Units]<>0,DIVIDE(BTA[D_Base_Units],BTA[D_EligibleBase_Units]),0)

measure:

Adj bse% = IF(sum(BTA[D_EligibleBase_Units])<>0,DIVIDE(sum(BTA[D_Base_Units]),sum(BTA[D_EligibleBase_Units]),blank()))

Any idea why this occurred?

[amitchandak]

@Anonymous , A column Adj Bse % would be simple Average when take in visual

while measure DIVIDE(sum(BTA[D_Base_Units]),sum(BTA[D_EligibleBase_Units]) is correct allocated average

They are not same

Average(table, A/B) is not same sum(A)/Sum(B)

[Anonymous]

Thank you for the quick reply, 

DIVIDE(A,B) will be how I want to put it, yet the result came out wrong in the calculated column, and it wont let me write like this in measure.

I dont understand why sum(A)/Sum(B) is right way to write it?

[v-rzhou-msft]

Hi @Anonymous ,

I think there may be duplicate key values like ID in your data table. Calcualted column will calculate by columns in each row, so you could only get the result from current row. However, measure could show you summarize result by sum/max function.

Here I build a sample to have a test by same code like yours.

You see in my data table, I have duplicate ID 1, so calcualte column will only return the result by row.

Measure should be the result you want.

If you want to get same result by calculated column, try this code.

Adj Bse% 2 =
VAR _D_Base_Units =
    CALCULATE ( SUM ( BTA[D_Base_Units] ), ALLEXCEPT ( BTA, BTA[ID] ) )
VAR _D_EligibleBase_Units =
    CALCULATE ( SUM ( BTA[D_EligibleBase_Units] ), ALLEXCEPT ( BTA, BTA[ID] ) )
RETURN
    IF (
        BTA[D_EligibleBase_Units] <> 0,
        DIVIDE ( _D_Base_Units, _D_EligibleBase_Units ),
        0
    )

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for the detail reply!

how do I know which column has been used as key value? and the way that my table set up are only having distinct values(My first colomn will be 1,2,3 instead of 1,1,2,3), shouldnt that fix the difference between measure and calculated colomns?

[v-rzhou-msft]

Hi @Anonymous ,

Key column is the column which you will use to build relationships in your data model, like ID in my Sample, categorys and so on. If ID is no duplicate (distinct 1,2,3), I think your code for calcualted column and measure will return the same result.

I couldn't reproduce your problem, please share a sample file with me and show me the result you want. This will make us easier to find the solution

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.