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
Dallas7890
Helper I
Helper I

I need help to write this same formula in Power BI to create this exact column J Edit Hours

Dallas7890_0-1656682715334.png

 

I am hoping that someone could help me to understand how to create this same column in power bi with these values

 

The Method values that are in column D are based on the static values below

Dallas7890_1-1656682954561.png

 

Thank you kindly,

Joanne

 

1 ACCEPTED SOLUTION
Nathaniel_C
Super User
Super User

Hi @Dallas7890 ,
Please share your pbix with us.
If you would share your pbix, or dummy up some values in Excel both for current and expected data. Please copy and paste them into your post, rather than doing a picture, we may be able to help you. 

Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Nathaniel





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

Proud to be a Super User!




View solution in original post

10 REPLIES 10
Anonymous
Not applicable

@Dallas7890 , to solve this, create three new columns in Data | Column Tools in Power BI.

MethodFactor = 
    SWITCH('Dallas7890_YourTable'[Method],
        "Re-edit", 2.5,
        "Packaged", 3.5,
        "EC", 1,
        "Gem re-send", 1,
        "Transcode", 0.75,
        "Redelivery", 1
    )

This just works out which multiple to use for each row based on the Method column.

 

EditHours = 
    ([Hours] * 24 * [MethodFactor])
    +
    ([Episodes] * TIME(0,15,0) * 24 )

 

The new EditHours column will give you a decimal number representing the number of EditHours.

 

 

EditHoursHH:MM = 
    FORMAT(INT([EditHours]), "00") & ":" & FORMAT(([EditHours] - INT([EditHours])) * 60, "00")

 

 The new EditHoursHH:MM column will give you the EditHours column formatted as text in the format HH:MM, where the hours can be greater than 23.

Putting these three calculated columns in gives me these results:

EylesIT_0-1656698117812.png

 

Hope this helps

Hi EylesIt

 

Can I please ask you another question? How can I now sum this column, EditHoursHH:MM to get the Total sum of Hours? If I try to change it from Text to any another datatype, I receive an error.

Dallas7890_0-1656708859165.png

 

Thanks,
Joanne

 

Anonymous
Not applicable

@Dallas7890 , for the sums, I think you can create two new measures:

 

SumEditHours = SUM('YourTable'[EditHours])
SumEditHoursHH:MM = FORMAT(INT([SumEditHours]), "00") & ":" & FORMAT(([SumEditHours] - INT([SumEditHours])) * 60, "00")

 

This gives me these totals:

EylesIT_1-1656757751846.png

 

@Anonymous, thank you so much,  these measures work perfectly! 

 

 

Hello EylesIT

 

Thank you so much for helping me. I really appreciate it. 

 

I typed this formula, but I keep getting this weird error message "Cannot convert value ?? of Type Text to type Number?

Dallas7890_0-1656701988326.png

 

 

EylesIT,

 

I figured it out. It was the Episodes column that was still text and I changed it to a number, and it all worked perfect. Thank you so much for all your help. Have a great day!

Anonymous
Not applicable

@Dallas7890 , can you please explain how the spreadsheet gets the Edit Hours of 19:30 from the other data in the spreadsheet? Is there is formula in cell J2?

Hello,

 

This is the Formula that is calculating this column on the excel sheet.

=SUM(E2*2.5 + C2*E16)

 

It will vary depending on the Rate per Method - 

Dallas7890_0-1656695860228.png

 

The E16 =  always 15 minutes

 

 

Nathaniel_C
Super User
Super User

Hi @Dallas7890 ,
Please share your pbix with us.
If you would share your pbix, or dummy up some values in Excel both for current and expected data. Please copy and paste them into your post, rather than doing a picture, we may be able to help you. 

Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Nathaniel





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

Proud to be a Super User!




Hello,

 

I really wish I could share the file but I don't see anywhere to do that. I did a snapshot of my pbix file data.  Really sorry, I'm still new to PBI and just trying to figure this all out. 

 

Dallas7890_0-1656692656882.png

 

Dallas7890_1-1656692849132.png

 

 

Thanks, Joanne

 

 

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.

Top Solution Authors