zero from divide function showing arbitrary value

[kohlivinayak]

Hi all 

I am using this below formula 

Current PC Contribution = DIVIDE(SUMX(Sheet1,IF(Sheet1[PC - 2]=Sheet1[max pc],Sheet1[Contrib val_3],0)),SUMX(Sheet1,IF(Sheet1[PC - 2]=Sheet1[max pc],Sheet1[Qty in Ton Adjusted],0)),0)

for values where contrib val 3 is -650 and qty in ton adjusted is 0 (replaced value from "-" to 0 in query editor) i am getting returned value as 29990370638585590000.00 

when i put filters and try to get this single record value for the current pc contribution it shows 0 only, also the grand total is showing correct value.

I am displaying this value in a matrix column.

[v-chuncz-msft]

@kohlivinayak,

I can not reproduce the issue. Share us the .pbix file if convenient. Do mask sensitive data before uploading.

[kohlivinayak]

@v-chuncz-msft

PBIX File:

https://drive.google.com/file/d/1CbhbmtplRSJxzu-CBVwMWe4zkHUuQj89/view?usp=sharing

the value for CSD is comming wrong, though the total is coming out correct.

[v-chuncz-msft]

@kohlivinayak,

The denominator is not exactly zero. A decimal number is always stored as a double-precision floating point value. You may change the data type of [Qty in Ton Adjusted] to fixed decimal number from the Modeling tab.

[kohlivinayak]

Tried modeling as well as rounding off not working.

[v-chuncz-msft]

@kohlivinayak,

It works for your .pbix above, please check again. As a kindly reminder, protect your privacy information.

[kohlivinayak]

can you share that file back

[v-chuncz-msft · Accepted solution]

@kohlivinayak,

Just select the column [Qty in Ton Adjusted] and change the data type as shown below.

[kohlivinayak]

Thanks finaly got it