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
MSBIJamie
Regular Visitor

Help me create two measurements?

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

1 ACCEPTED SOLUTION

Hi @MSBIJamie,

 

You may download my solution file form here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

12 REPLIES 12
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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?Capture.JPG

Hi,

 

Share the file and also show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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:

  1. I would like to use the N-Column, the active projects column, as a slicer.... the projects without an X are inactive projects that are hidden and the charts do not need to reflect their information. 
  2. I would like a chart that displays the total sum of all the weekly hours each designer estimates they'll need for their active projects.  That way I could monitor who already has 40 hours a week of work ahead of them and know not to assign them any more projects.
  3. I would like a percentage calculated for each job... I want to add the accrued hours ad the weekly estimate and then divided by the estimate of total hours... I want to see it as a percentage on a bar chart so I could see which jobs are in danger of going over the estimate of time we're allocated for them.  (Since we have multiple projects for each client, I would like each bar on the graph labeled by "Client / Design Project Name).
  4. I would like a way to filter the bar graph so I could just see only projects that are associated with Tommy, Vince, Donnie, etc .... so that I could take a closer look of just their projects.20170925_091709.jpgCapture.JPG

Hi,

 

Share the Excel file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you very very much!!  This is a big help!! 

You are welcone.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Interkoubess
Solution Sage
Solution Sage

Hi @MSBIJamie,

 

Could you please give us a sample data with your expected results and then we can try to help.

 

Thank you

 

Ninter

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.