Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
Proud to be a Super User!
@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
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
@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:
@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?
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!
@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 -
The E16 = always 15 minutes
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
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.
Thanks, Joanne
User | Count |
---|---|
43 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |