Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Everyone:
I'm new to Power BI and I'm trying to create two measurements. I'm not having lunch unforutnately. I was hoping someone could help me out? The columns are all on the same data sheet (CRO 3D). The column names are Lead Designer, Company, Project name, Estimate of total Hours, Accrued Hours, Weekly Estimate and X. (Projects ferquently switch between active and inactive, so I have a column in execl with just an X for the active ones so that I can easily filter to see only the active stuff...)
For the first formula, I would like to create a percentage of how many of the estimated hours used. First I added a new column named Percentage of Hours Used Up. Then I tried the following formula:
Percentage of Hours Used Up = 'CRO 3D'[Accrued Hours] + 'CRO 3D'[Weekly Estimate] / 'CRO 3D'[Estimate of Total Hours]
I received the following message:
Cannot convert value " of type Text to type Number
I wanted to use this calculated column to create a bar graph showing which projects are in danger of going over 100% of their estimated hours.
I also wanted to great a second graph that would add up the weekly estimates of each Lead Designer, so I could see who already has 40 hours of work allocated to them and who can take on more work...I would need to add up all of the rows that have an X in them for each designer...but I'm unable to figure out how to begin to do that? (from what little i know of PowerBI, it seems to me that would be more of a meassurement of than a calcuated column.)
Can someone let me know how to build the reports I need?
Thanks,
Jamie
Solved! Go to Solution.
Hi @MSBIJamie,
You may download my solution file form here.
Hope this helps.
Hi,
That error messahe indicates that atleast one numeric column in your formula is of text data type. Using Query Editor, specify the type as Number. For more specific help, share the link from where i can download your file.
Hi Ashish & Ninter: Thank you so much for your help!!
Ashish- thank you, your advice worked!! The formula is calculating the percentages now that I used Query Editor to specify the column as a Number.
Could you guys help me with two follow up question?
1. The formula calculates some of the %'s I need, but if you look at the screencap I'm uploading, you'll see that some of them are way off. Any idea how I could fix that?
2. I wanted to create a second measurement but not sure where to begin. I would like to add up all of of the weekly estimates for each indivisual designer IF there is an X in the far right column (so IF there is an X, then add up of Tom's weekly hours seperately, Donnie's weekly hours seperately, Dan's weekly hours seperately, etc... so I could see that they are not getting more than 40 hours of work per week. Is that possible?
Hi,
Share the file and also show the expected result.
Hi Ashish:
Thank you for responding again!! I'm attaching a sketch of the kind of charts I'd like to build. Ideally, I would like to build the following dashboard:
Hi,
Share the Excel file.
Hi Ashish:
I'm not sure how to upload an excel to the message boards...plus it contains info about our clients I'd prefer not to post...would it be okay if I were to send it via the facebook link you have in your profile; or do you have private email address I could use?
Hi,
I cannot share my personal e-mail address here.
Totally understandable!! I just sent you a message via facebook. Thank you again, I really appreciate all your help! So kind of you!!
Hi @MSBIJamie,
You may download my solution file form here.
Hope this helps.
Thank you very very much!! This is a big help!!
You are welcone.
Hi @MSBIJamie,
Could you please give us a sample data with your expected results and then we can try to help.
Thank you
Ninter
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |