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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
spandy34
Responsive Resident
Responsive Resident

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

spandy34
Responsive Resident
Responsive Resident

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
spandy34
Responsive Resident
Responsive Resident

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")


======

 
spandy34
Responsive Resident
Responsive Resident

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

spandy34
Responsive Resident
Responsive Resident

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.