topic I need help to write this same formula in Power BI to create this exact column J Edit Hours in DAX Commands and Tips

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

Dallas7890 — Fri, 01 Jul 2022 13:43:06 GMT

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

Thank you kindly,

Joanne

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

Nathaniel_C — Fri, 01 Jul 2022 14:54:14 GMT

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

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

Dallas7890 — Fri, 01 Jul 2022 16:29:30 GMT

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.

Thanks, Joanne

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

Anonymous — Fri, 01 Jul 2022 16:48:14 GMT

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?

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

Dallas7890 — Fri, 01 Jul 2022 17:19:42 GMT

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 -

The E16 = always 15 minutes

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

Anonymous — Fri, 01 Jul 2022 17:55:26 GMT

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:

Hope this helps

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

Dallas7890 — Fri, 01 Jul 2022 19:00:29 GMT

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?

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

Dallas7890 — Fri, 01 Jul 2022 19:41:48 GMT

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!

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

Dallas7890 — Fri, 01 Jul 2022 20:55:11 GMT

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.

Thanks,
Joanne

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

Anonymous — Sat, 02 Jul 2022 10:39:03 GMT

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:

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

Dallas7890 — Sat, 02 Jul 2022 10:56:48 GMT

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