Forum Discussion

MSBIJamie's avatar
MSBIJamie
Regular Visitor
8 years ago
Solved

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

Need Help

12 Replies

  • Interkoubess's avatar
    Interkoubess
    Solution Sage
    8 years ago

    Hi MSBIJamie,

     

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

     

    Thank you

     

    Ninter

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    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.

    • MSBIJamie's avatar
      MSBIJamie
      Regular Visitor
      8 years ago

      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?