Calculated column and calculated measure with same formula giving different results

[Madhuris_20]

Hi,

I want my formula to give correct result in calculated column, but when taking it in measure it is showing correct value.

I need it as column so that to add it in my pie chart.

Formula for both H/M measure & H/M Column are exact same.

[HIT/Miss Measure] is calculated Measure.

Can you please help me to get correct values in H/M column (exact match as H/M measure).

[V-lianl-msft]

Hi @Madhuris_20 ,

Measures will be evaluated on demand and respond to the context. So there couldn't be a measure in the middle steps. When the data is loaded, the calculated column will be evaluated. 

There may be workaround. Please share the sample data and the formula of [HIT/Miss Measure].

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

[Madhuris_20]

Hi @V-lianl-msft ,

HIT/Miss Measure = IF( CONVERT([Calculated measure1],DOUBLE) >= 0.75 && CONVERT([Calculated measure1],DOUBLE) <= 1.25 , 0,1)

Calculated measure1 = IF(ISBLANK(IF(SELECTEDVALUE('Table1'[DATE]),
CALCULATE(SUM('Table1'[Sales])/SUM('Table2'[forecast])),
IF(NOT(SELECTEDVALUE('Table1'[DATE])),CALCULATE(TOTALYTD(SUM('Table1'[Sales]),'Table1'[DATE])/(SUM('Table2'[forecast])*12))))),0,(IF(SELECTEDVALUE('Table1'[DATE]),
CALCULATE(SUM('Table1'[Sales])/SUM('Table2'[forecast])),
IF(NOT(SELECTEDVALUE('Table1'[DATE])),CALCULATE(TOTALYTD(SUM('Table1'[Sales]),'Table1'[DATE])/(SUM('Table2'[forecast])*12))))))

It will be very helpful if you will provide any alternate workaround for the same.

Thank you in Advance!

[amitchandak]

@Madhuris_20 , Calculated column work at row level, while measure work at the grouped data level. So very little chance they will give the same result. Unless designed to do so

refer for diff if needed

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

[Madhuris_20]

Hi @amitchandak ,

Thank you for your immediate reply!

It will be very helpful if you will provide any alternate solution for the same, as it is must to have H/M column to add it in Legend section of pie chart.

Or is there any function to make column forcefully work as measure & not at Row Level.

Thank you in advance!