Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Custom Column to round number up to 0.5

Hi All

 

I wonder if someone could help me out with a challenge I'm facing. Im calculating pro-rate annual leave and need to round the result up to the nearest 0.5.

 

My column looks like this

= Table.AddColumn(#"Changed Type2", "Pro-Rata", each ([Allowance]/12)*[Months Elapsed],Int64.Type)

 

and I was hoping there's something I can do to get e.g. 2.25 to round to 2.5, or 3.17 to round to 3.5 etc.

5 REPLIES 5
Anonymous
Not applicable

Thanks for all the suggestions. I ended up going down a route I used in Excel many years ago.

 

Rounded up 2 x the number and then divided it by 2. Seems to work perfectly. I did expect there to be a CEILING/FLOOR type function but aparently not. No matter, I think the method I've used does the job.

Padycosmos
Solution Sage
Solution Sage

Hope this helps though this may not be the most efficient way:

Padycosmos_0-1676623062812.png

 

 

Anonymous
Not applicable

Thank you, that does work. I wanted to go on and use that rounded number in another custom column but I'm not sure that I can to that having added a column in this way. Is there a way to use Custom Column in Transform Data to acheive same? 

you can try this in PQ

= Table.AddColumn(#"Changed Type", "Custom", each Number.RoundUp([Column1]/0.5)*0.5)

1.PNG 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ryan_mayu
Super User
Super User

@Anonymous 

you can try this

Column = CEILING('Table'[Column1],0.5)

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.