Forum Discussion

chrisgehm's avatar
chrisgehm
Helper III
9 years ago

Divide and TOP 10

Hi everyone!   I have 1 table in excel with 3 columns   Amount1 - Amount2 - Provider   I've create a new column in Power BI that makes the difference between Amount1 and Amount2 Lets call it D...
Need Help
chrisgehm's avatar
chrisgehm
Helper III
9 years ago

v-yulgu-msft

When i make the division with "/" the result shows me: NaN or -Infinity

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

Hi chrisgehm,

 

Please click the arrow down under 'Filter Type' not 'Show items when the value:' to select 'Top N' option.

 

Please check whether there existing blank or 0 values in column [Difference] and [Amount1], blank value or 0 value will lead to Nan or Infinity when using divide.

 

If that is the case, please try this formula:

New Column = IF(ISBLANK('Table'[Amount1]) || ISBLANK('Table'[Difference]) || 'Table'[Amount1])=0 || 'Table'[Difference]=0,BLANK(), DIVIDE('Table'[Difference],'Table'[Amount1])))

Regards,
Yuliana Gu