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
spandy34
Post Patron
Post Patron

Time Column Calculation Percentage

I have a table called Procurement_Main_Data and want to create a column that will put a yes  if the Delivered Duration is 10% greater than the Planned Duration otherwise say No

 

The fields Delivered Duration and Planned Duration are Short Time fields

 

Please can someone help

 

Many thanks

 

 

 

 

spandy34_0-1679573703050.png

@Ahmedx

1 ACCEPTED SOLUTION

because you write the measure incorrectly.
this is how you should write it:

10% Over =
if([Variance %]>0.1,"Yes","No

View solution in original post

9 REPLIES 9
Ahmedx
Super User
Super User

 

 

10% Over = 
VAR _limit = MINUTE([Planned Duration])*(1+0.1)
VAR _Results = if(MINUTE([Delivered Duration])>_limit,"YES","NO")
RETURN
_Results

10% Under = 
VAR _limit = MINUTE([Planned Duration])*(1-0.1)
VAR _Results = if(MINUTE([Delivered Duration])<_limit,"YES","NO")
RETURN
_Results
------------
OR
10% Over = 
VAR _limit = MINUTE([Planned Duration])*1.1
VAR _Results = if(MINUTE([Delivered Duration])>_limit,"YES","NO")
RETURN
_Results

10% Under = 
VAR _limit = MINUTE([Planned Duration])*0.9
VAR _Results = if(MINUTE([Delivered Duration])<_limit,"YES","NO")
RETURN
_Results

 

 

 

Screen Capture #739.pngScreen Capture #740.png

Ive added another column called Variance Column.  The majority of rows are fine for the 10% Under and10% Over Columns but was wondering why the records filtered in the 10% Over Column is saying 'No' when the Variance % is 1000%.  The Variance % field is Percentage format and Data Type Decimal Number

 

Any ideas? Thank you for your ongoing support

 

spandy34_0-1679618485210.png

 

because you write the measure incorrectly.
this is how you should write it:

10% Over =
if([Variance %]>0.1,"Yes","No

Thank you thats done it.  Really appreciate your help as always:)

10% Over = 
VAR _limit = 
(HOUR([Planned Duration]) *3600 + MINUTE([Planned Duration])* 60 + SECOND([PlannedDuration]))*1.1
VAR _Results = if(
       HOUR('Table'[Delivered Duration]) *3600 + MINUTE('Table'[Delivered Duration])* 60 + SECOND('Table'[Delivered Duration])
>_limit,"YES","NO")
RETURN

10% Under = 
VAR _limit = 
(HOUR([Planned Duration]) *3600 + MINUTE([Planned Duration])* 60 + SECOND([Planned Duration]))*0.9
VAR _Results = if(HOUR('Table'[Delivered Duration]) *3600 + MINUTE('Table'[Delivered Duration])* 60 + SECOND('Table'[Delivered Duration])<_limit,"YES","NO")
RETURN
_Results

Screen Capture #741.pngScreen Capture #742.png

Ahmedx
Super User
Super User

is 10% greater than the Planned Duration
Column = if(MINUTE([Delivered Duration])-MINUTE([Planned Duration]) >10,"YES","NO")
or
Column = if(MINUTE([Delivered Duration])/MINUTE([Planned Duration]) -1> 0.1,"YES","NO")


======

 

Hi so I need it to say yes if it great than 10% 

 

eg 10% Over Column 

Planned Duration = 20 min and the Delivered Duration = 24 minutes 

10% of 20 minutes planned is 22 so if the Delivered Duration is more than 22 I want it to say Yes, otherwise no

 

also in the 10% Under Column

Planned Duration =  20 minutes  Delivered Duration = 12 minutes 

10% under of 20 minutes planned is 18 so if the Delivered Duration is less than 18 I want it to say Yes, otherwise No

 

Ahmedx
Super User
Super User

according to your table, there will be "NO" everywhere, I correctly understood your goal?

Screen Capture #735.png

There is a lot of data.  

I want to create a column that will put a yes  if the Delivered Duration is 10% greater than the Planned Duration otherwise say No and call it 'over 10%' also

 

I create a column that will put a yes  if the Delivered Duration is 10% less than the Planned Duration otherwise say No and call it 'under 10%'

 

spandy34_9-1679582347916.png

 

 

 

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.