topic Re: SUM and MAX in Desktop
https://community.powerbi.com/t5/Desktop/SUM-and-MAX/m-p/901288#M431989
<P>Thanks <LI-USER uid="82403"></LI-USER> </P><P>Sorry I am quite new to Power BI. I clicked on the ribbon to make a New table enterring your formula, and got the same error, This is my formula that I had been using: </P><P> </P><DIV><DIV><SPAN>Table 2 = </SPAN></DIV><DIV><SPAN>ADDCOLUMNS(</SPAN></DIV><DIV><SPAN>DISTINCT(vw_DES_Outcome_Tracking_13_26_52Weeks[Jobseeker Id]), </SPAN></DIV><DIV><SPAN>"Hours Behind", CALCULATE(SUM(vw_DES_Outcome_Tracking_13_26_52Weeks[Weekly Hours]), ALLEXCEPT(vw_DES_Outcome_Tracking_13_26_52Weeks,vw_DES_Outcome_Tracking_13_26_52Weeks[Jobseeker Id]) - CALCULATE(MAX(vw_DES_Outcome_Tracking_13_26_52Weeks[Anchor Week Diff]), ALLEXCEPT(vw_DES_Outcome_Tracking_13_26_52Weeks,vw_DES_Outcome_Tracking_13_26_52Weeks[Jobseeker Id]) * CALCULATE(MAX(vw_DES_Outcome_Tracking_13_26_52Weeks[BM Hours]), ALLEXCEPT(vw_DES_Outcome_Tracking_13_26_52Weeks,vw_DES_Outcome_Tracking_13_26_52Weeks[Jobseeker Id]))</SPAN></DIV><DIV><SPAN>)))</SPAN></DIV></DIV>Wed, 15 Jan 2020 22:07:18 GMTadnanzakir2020-01-15T22:07:18ZSUM and MAX
https://community.powerbi.com/t5/Desktop/SUM-and-MAX/m-p/898951#M430915
<P>Hi Guys. </P><P>Requesting assistance on the following: </P><P> </P><P>I have a table where each unique person ID has hours entered against a week. </P><P> </P><TABLE><TBODY><TR><TD>ID</TD><TD>Weeks since placement</TD><TD>Hours completed each week</TD><TD>Target hours to be completed each week</TD></TR><TR><TD>a</TD><TD>1</TD><TD>23</TD><TD>15</TD></TR><TR><TD>a</TD><TD>2</TD><TD>21</TD><TD>15</TD></TR><TR><TD>a</TD><TD>3</TD><TD>42</TD><TD>15</TD></TR><TR><TD>a</TD><TD>4</TD><TD>23</TD><TD>15</TD></TR><TR><TD>a</TD><TD>5</TD><TD>21</TD><TD>15</TD></TR><TR><TD>a</TD><TD>6</TD><TD>23</TD><TD>15</TD></TR><TR><TD>b</TD><TD>1</TD><TD>22</TD><TD>23</TD></TR><TR><TD>b</TD><TD>2</TD><TD>21</TD><TD>23</TD></TR><TR><TD>b</TD><TD>3</TD><TD>20</TD><TD>23</TD></TR><TR><TD>c</TD><TD>1</TD><TD>15</TD><TD>30</TD></TR><TR><TD>c</TD><TD>2</TD><TD>16</TD><TD>30</TD></TR></TBODY></TABLE><P> </P><P>What I am trying to do is have a single record for an ID which shows the number of hours behind. </P><P>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). </P><P>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))</P><P>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</P><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="PBI.JPG" style="width: 999px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/225132iD6B98ED562D64C82/image-size/large?v=1.0&px=999" title="PBI.JPG" alt="PBI.JPG" /></span></P><P> </P>Tue, 14 Jan 2020 07:10:08 GMThttps://community.powerbi.com/t5/Desktop/SUM-and-MAX/m-p/898951#M430915adnanzakir2020-01-14T07:10:08ZRe: SUM and MAX
https://community.powerbi.com/t5/Desktop/SUM-and-MAX/m-p/898969#M430922
<P>Hi <LI-USER uid="98024"></LI-USER> </P><P>you can create a calculated table like</P><P> </P><LI-CODE lang="markup">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]))
)</LI-CODE><P><STRONG><I><FONT color="#ababab">do not hesitate to give a kudo to useful posts and mark solutions as solution</FONT></I></STRONG></P>Tue, 14 Jan 2020 07:17:23 GMThttps://community.powerbi.com/t5/Desktop/SUM-and-MAX/m-p/898969#M430922az382020-01-14T07:17:23ZRe: SUM and MAX
https://community.powerbi.com/t5/Desktop/SUM-and-MAX/m-p/899071#M430966
<P>Untill you can write that yourself - I would ALWAYS reccomend having that in 3 or 4 sepperate <STRIKE>rows</STRIKE> Collumns, unless your data set already has more than Many rows and millions of lines. - </P><P> </P><P>Also I would do a behind pr week collumn - since this is also a good way to see a development if you plot it.</P><P> </P><P>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</P><P>Insert a new collumn with this formular if you just want the result:</P><P> </P><LI-CODE lang="markup">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:</LI-CODE><P> </P><P><BR />New collumn Behind pr. week:</P><P> </P><P> </P><LI-CODE lang="markup">Behind this week = 'Table'[Target hours to be completed each week]-'Table'[Hours completed each week] </LI-CODE><P> </P><P> </P><P>Hope this help... but also hope you add more columns to better understand the formulars and calculatitions, this has helped me alot of times.</P><P> </P>Wed, 22 Jan 2020 06:30:06 GMThttps://community.powerbi.com/t5/Desktop/SUM-and-MAX/m-p/899071#M430966Rygaard2020-01-22T06:30:06ZRe: SUM and MAX
https://community.powerbi.com/t5/Desktop/SUM-and-MAX/m-p/899314#M431083
Thanks az38. I believe all that info is on a single calculated column?Tue, 14 Jan 2020 11:16:02 GMThttps://community.powerbi.com/t5/Desktop/SUM-and-MAX/m-p/899314#M431083adnanzakir2020-01-14T11:16:02ZRe: SUM and MAX
https://community.powerbi.com/t5/Desktop/SUM-and-MAX/m-p/899318#M431086
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.Tue, 14 Jan 2020 11:18:27 GMThttps://community.powerbi.com/t5/Desktop/SUM-and-MAX/m-p/899318#M431086adnanzakir2020-01-14T11:18:27ZRe: SUM and MAX
https://community.powerbi.com/t5/Desktop/SUM-and-MAX/m-p/900278#M431541
<P>Hi AZ38. The solution worked to some extent, it gave me the following error: </P><P>"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."</P>Wed, 15 Jan 2020 06:20:36 GMThttps://community.powerbi.com/t5/Desktop/SUM-and-MAX/m-p/900278#M431541adnanzakir2020-01-15T06:20:36ZRe: SUM and MAX
https://community.powerbi.com/t5/Desktop/SUM-and-MAX/m-p/900330#M431567
<P>Hi <LI-USER uid="98024"></LI-USER> </P><P>It is a new calculated <STRONG>table</STRONG> (Modeling ribbon -> New table)</P><P> </P><P><STRONG><I><FONT color="#ababab">do not hesitate to give a kudo to useful posts and mark solutions as solution</FONT></I></STRONG></P><P> </P><P> </P>Wed, 15 Jan 2020 07:08:16 GMThttps://community.powerbi.com/t5/Desktop/SUM-and-MAX/m-p/900330#M431567az382020-01-15T07:08:16ZRe: SUM and MAX
https://community.powerbi.com/t5/Desktop/SUM-and-MAX/m-p/901288#M431989
<P>Thanks <LI-USER uid="82403"></LI-USER> </P><P>Sorry I am quite new to Power BI. I clicked on the ribbon to make a New table enterring your formula, and got the same error, This is my formula that I had been using: </P><P> </P><DIV><DIV><SPAN>Table 2 = </SPAN></DIV><DIV><SPAN>ADDCOLUMNS(</SPAN></DIV><DIV><SPAN>DISTINCT(vw_DES_Outcome_Tracking_13_26_52Weeks[Jobseeker Id]), </SPAN></DIV><DIV><SPAN>"Hours Behind", CALCULATE(SUM(vw_DES_Outcome_Tracking_13_26_52Weeks[Weekly Hours]), ALLEXCEPT(vw_DES_Outcome_Tracking_13_26_52Weeks,vw_DES_Outcome_Tracking_13_26_52Weeks[Jobseeker Id]) - CALCULATE(MAX(vw_DES_Outcome_Tracking_13_26_52Weeks[Anchor Week Diff]), ALLEXCEPT(vw_DES_Outcome_Tracking_13_26_52Weeks,vw_DES_Outcome_Tracking_13_26_52Weeks[Jobseeker Id]) * CALCULATE(MAX(vw_DES_Outcome_Tracking_13_26_52Weeks[BM Hours]), ALLEXCEPT(vw_DES_Outcome_Tracking_13_26_52Weeks,vw_DES_Outcome_Tracking_13_26_52Weeks[Jobseeker Id]))</SPAN></DIV><DIV><SPAN>)))</SPAN></DIV></DIV>Wed, 15 Jan 2020 22:07:18 GMThttps://community.powerbi.com/t5/Desktop/SUM-and-MAX/m-p/901288#M431989adnanzakir2020-01-15T22:07:18Z