Forum Discussion

JoeKat07's avatar
JoeKat07
Frequent Visitor
9 years ago

summing hours by project ID

I'm trying to create a new column that turns the first table into the second table. I'm trying to sum the hours that were done on each project.

 

Here is my code so far: NewColumn = sumx('Test Table', 'Test Table'[Hours])

 

 

 

Thanks!!!!

General Comment
Need Help
Tips and Tricks
Tutorial Requests

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    JoeKat07 You need to use a measure, not a calculated column. 

     

    Measure = SUM('Test Table'[Hours])
    • JoeKat07's avatar
      JoeKat07
      Frequent Visitor
      9 years ago

      Great! that worked! now im trying to display the projects that have over 100 hours, between 100 and 95, between 95 and 90, and below 90. The first one works great! i've made a calculated column that is able to show that 5,6,8,9, and 10 all have over 100 hours. However when i try and make a new calculated column that shows the id's that are between 100 and 95 i get a circular reference error.

       

       

       

      i'm not sure where i have gone astray.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        JoeKat07 You can do the following static segmentation for this. (change to fit your needs)

         

        First, create a seperate bucket table: (Buckets) - do not add a relationship to the other table, leave it disconnected.

        Bucket	Min	Max
> 100	101	9999999999
100-95	96	100
95-90	91	95
< 90	0	90

        then you can use the above table as a reference for the calculated column. Create the calculated column in your 'Test Table'

        Group = CALCULATE (
    VALUES ( 'Buckets'[Bucket] ),
    FILTER (
        'Buckets',
        'Test Table'[Hours] >= 'Buckets'[Min] 
        && 'Test Table'[Hours] <= 'Buckets'[Max] 
    )
)

        This gives you the bucket, and you can use the measure hours in your visual with the bucket and it works showing you something like this:

         

        If you need to sort it, then add one more column to the bucket table to order them 1,2,3,4 and you can use the "Sort by Column" in the modeling header when you are in the data section.

         

         

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi JoeKat07,

     

    Could you please mark the right post if it worked? More about this topic, please feel free to post here.

    Best Regards!
    Dale