cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

integer numbers for new column calculation

Hi,

I have a small issue: my dataframe contains two columns - OrderedQuantity and DeliveredQuantity which contains decimal and integer values. I need to make a new column like this:

=0 in those rows where OrderedQuantity and DeliveredQuantity has integer values

= OrderedQuantity-DeliveredQuantity where OrderedQuantity and DeliveredQuantity has decimal values.

 

I was thinking about using ROUND() function:

= if [OrderedQuantity]<>ROUND(OrderedQuantity,0) and ... then [OrderedQuantity]-[DeliveredQuantity]

But it is not working. Can someone suggest other solution?

 

Thank you a lot

 

3 REPLIES 3
Microsoft
Microsoft

Hi SkaistePapuskai,

 

I tried the round function and it works, please try the formula when creating the column:

Column = if(value(Sheet1[Sales])<>round(Sheet1[Sales],0)&&value(Sheet1[Sales1])<>round(Sheet1[Sales1],0),

                  Sheet1[Sales]-Sheet1[Sales1],

                   0)

See the result:

6.PNG

If any further help needed, please feel free to post back.

Regards

 

New Member

If you don't need the decimals at all, you can format the data type of your columns to "WHOLE NUMBER".

Community Champion
Community Champion

@SkaistePapuskai Created calculated column as below,

 

= IF( IFERROR(SEARCH(".",YOUR COLUMN),0) > 0, RESULT IF DECIMAL, RESULT IF INTEGER ) 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors