Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

SUM and MAX

Hi Guys. 

Requesting assistance on the following: 

 

I have a table where each unique person ID has hours entered against a week. 

 

IDWeeks since placementHours completed each weekTarget hours to be completed each week
a12315
a22115
a34215
a42315
a52115
a62315
b12223
b22123
b32023
c11530
c21630

 

What I am trying to do is have a single record for an ID which shows the number of hours behind. 

For example, for ID b, the person has completed 63 hours in total (22+21+20) where as the person should've done 69 according to target hours (23+23+23). 

Is there a way I can have a single record which shows SUM(total hours completed) - (MAX(weeks since placement)*(Target hours to be completed))

The issue is, on the table it comes up as each row of unique of person with different weeks in each row. I wanted to summarize it in the format below

 
 
 

 

Need Help
  • az38's avatar
    az38
    6 years ago

    Hi Anonymous 

    you can create a calculated table like

     

    Table 2 = 
ADDCOLUMNS(
    DISTINCT('Table'[ID]);
    "Hours Behind";CALCULATE(SUM('Table'[Hours completed each week]);ALLEXCEPT('Table';'Table'[ID]))-CALCULATE(MAX('Table'[Weeks since placement]);ALLEXCEPT('Table';'Table'[ID]))*CALCULATE(MAX('Table'[Target hours to be completed each week]);ALLEXCEPT('Table';'Table'[ID]))
)

    do not hesitate to give a kudo to useful posts and mark solutions as solution

  • Rygaard's avatar
    Rygaard
    6 years ago

    Untill you can write that yourself - I would ALWAYS reccomend having that in 3 or 4 sepperate rows Collumns, unless your data set already has more than Many rows and millions of lines. - 

     

    Also I would do a behind pr week collumn  - since this is also a good way to see a development if you plot it.

     

    Also Since you have a Target pr week in each line  - why not use that  --- so if you ever encounter that it can warry over a project periond your calculations is still correct

    Insert a new collumn with this formular if you just want the result:

     

    Behind total = CALCULATE(SUM('Table'[Target hours to be completed each week]); FILTER('Table';'Table'[ID]=EARLIER('Table'[ID]))) -CALCULATE(SUM('Table'[Hours completed each week]); FILTER('Table';'Table'[ID]=EARLIER('Table'[ID]))) ​
 
New collumn Behind pr. week:

     


    New collumn Behind pr. week:

     

     

    Behind this week = 'Table'[Target hours to be completed each week]-'Table'[Hours completed each week]

     

     

    Hope this help... but also hope you add more columns to better understand the formulars and calculatitions, this has helped me alot of times.

     

7 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

    you can create a calculated table like

     

    Table 2 = 
ADDCOLUMNS(
    DISTINCT('Table'[ID]);
    "Hours Behind";CALCULATE(SUM('Table'[Hours completed each week]);ALLEXCEPT('Table';'Table'[ID]))-CALCULATE(MAX('Table'[Weeks since placement]);ALLEXCEPT('Table';'Table'[ID]))*CALCULATE(MAX('Table'[Target hours to be completed each week]);ALLEXCEPT('Table';'Table'[ID]))
)

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      Thanks az38. I believe all that info is on a single calculated column?
      • az38's avatar
        az38
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi Anonymous 

        It is a new calculated table (Modeling ribbon -> New table)

         

        do not hesitate to give a kudo to useful posts and mark solutions as solution

         

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi AZ38. The solution worked to some extent, it gave me the following error: 

      "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

  • Rygaard's avatar
    Rygaard
    Icon for Resolver I rankResolver I
    6 years ago

    Untill you can write that yourself - I would ALWAYS reccomend having that in 3 or 4 sepperate rows Collumns, unless your data set already has more than Many rows and millions of lines. - 

     

    Also I would do a behind pr week collumn  - since this is also a good way to see a development if you plot it.

     

    Also Since you have a Target pr week in each line  - why not use that  --- so if you ever encounter that it can warry over a project periond your calculations is still correct

    Insert a new collumn with this formular if you just want the result:

     

    Behind total = CALCULATE(SUM('Table'[Target hours to be completed each week]); FILTER('Table';'Table'[ID]=EARLIER('Table'[ID]))) -CALCULATE(SUM('Table'[Hours completed each week]); FILTER('Table';'Table'[ID]=EARLIER('Table'[ID]))) ​
 
New collumn Behind pr. week:

     


    New collumn Behind pr. week:

     

     

    Behind this week = 'Table'[Target hours to be completed each week]-'Table'[Hours completed each week]

     

     

    Hope this help... but also hope you add more columns to better understand the formulars and calculatitions, this has helped me alot of times.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      Thanks Rygaard. That's a good advice thank you for that. Having 3 or 4 columns would be easier to work with. I'll try to use the formula you provided. Cheers for that. Will let you know if it works.