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 Tabl...
General Comment
Need Help
Tips and Tricks
Tutorial Requests
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.

       

       

      • pattersonm8's avatar
        pattersonm8
        Helper II
        6 years ago

        this works awesome if only row of data per grouping, but how do you get it to look at the total of all the rows with that same category (month, state, etc.), it said to do as measure instead of column, but that is not working using that measure in Calculated column and only works if hard code like "MI" for Michigan, but want a set of data to do for each State, how do you reference back to only give sum or whatever of all the rows that are the same month/state, etc. as that row?  have tried RELATED and VAR but nothing works