topic Need to understand how/what is formula is doing | This Project was handed off to me | Please Help in Desktop
https://community.powerbi.com/t5/Desktop/Need-to-understand-how-what-is-formula-is-doing-This-Project-was/m-p/2078634#M774008
<P>I am dealing with event log data - Successful/Failed Transaction Metrics. I want to calculate hours saved / ROI using an ROI Table. I will attach a PBIX File, Documentation, Dataset, ROI Static File. I want to understand what this formula is doing:</P><P> </P><DIV><DIV><SPAN>Hours Saved = SUMX(JobsDB, RELATED(jobsROI[timeValueMin]))/60</SPAN></DIV><DIV> </DIV><DIV><SPAN>Here is a sample of my fact table (JobsDB)</SPAN></DIV><DIV><SPAN><SPAN>jobIdjobStartjobEndjobStateprocessNameProcessing Time</SPAN></SPAN> <TABLE><TBODY><TR><TD>889</TD><TD>3/16/2019 12:12</TD><TD>3/16/2019 15:20</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.1</TD></TR><TR><TD>900</TD><TD>3/19/2019 8:00</TD><TD>3/19/2019 11:23</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.4</TD></TR><TR><TD>901</TD><TD>3/19/2019 12:18</TD><TD>3/19/2019 15:28</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.2</TD></TR><TR><TD>902</TD><TD>3/19/2019 16:27</TD><TD>3/19/2019 19:51</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.4</TD></TR><TR><TD>906</TD><TD>3/20/2019 23:34</TD><TD>3/21/2019 2:33</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.0</TD></TR><TR><TD>913</TD><TD>3/22/2019 9:33</TD><TD>3/22/2019 12:41</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.1</TD></TR><TR><TD>914</TD><TD>3/22/2019 14:08</TD><TD>3/22/2019 17:06</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.0</TD></TR><TR><TD>918</TD><TD>3/23/2019 8:00</TD><TD>3/23/2019 11:16</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.3</TD></TR><TR><TD>919</TD><TD>3/23/2019 11:49</TD><TD>3/23/2019 14:53</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.1</TD></TR><TR><TD>921</TD><TD>3/23/2019 20:28</TD><TD>3/23/2019 23:35</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.1</TD></TR><TR><TD>927</TD><TD>3/25/2019 15:24</TD><TD>3/25/2019 18:40</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.3</TD></TR><TR><TD>928</TD><TD>3/25/2019 18:52</TD><TD>3/25/2019 21:53</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.0</TD></TR><TR><TD>929</TD><TD>3/25/2019 23:29</TD><TD>3/26/2019 2:49</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.3</TD></TR><TR><TD>930</TD><TD>3/26/2019 8:00</TD><TD>3/26/2019 11:17</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.3</TD></TR><TR><TD>947</TD><TD>3/30/2019 16:35</TD><TD>3/30/2019 19:36</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.0</TD></TR></TBODY></TABLE></DIV></DIV><P> </P><P>Here is the ROI Table that I'm pointing to in the SUMX equation:</P><P> </P><TABLE><TBODY><TR><TD>processName</TD><TD>timeValueMin</TD><TD>itemCost</TD></TR><TR><TD>Invoices-Processing_MKT</TD><TD>15</TD><TD>93</TD></TR><TR><TD>Pipeline-Analysis_SAP</TD><TD>30</TD><TD>76</TD></TR><TR><TD>Costs-Reconciliation_FIN</TD><TD>12</TD><TD>81</TD></TR><TR><TD>CustData-Reconciliation_SAP</TD><TD>24</TD><TD>82</TD></TR><TR><TD>Salesforce-Analysis_SAP</TD><TD>21</TD><TD>82</TD></TR><TR><TD>Salesforce-Processing_SAP</TD><TD>14</TD><TD>88</TD></TR><TR><TD>Clients-Reconciliation_SAP</TD><TD>18</TD><TD>85</TD></TR><TR><TD>Invoices-Reconciliation_MKT</TD><TD>7</TD><TD>72</TD></TR><TR><TD>Logs-Analysis_HR</TD><TD>7</TD><TD>69</TD></TR><TR><TD>Logs-Reconciliation_SAP</TD><TD>7</TD><TD>54</TD></TR><TR><TD>Invoices-Classification_FIN</TD><TD>29</TD><TD>54</TD></TR><TR><TD>CustData-Analysis_MKT</TD><TD>7</TD><TD>76</TD></TR><TR><TD>Logs-Classification_HR</TD><TD>27</TD><TD>99</TD></TR><TR><TD>Clients-Processing_MKT</TD><TD>16</TD><TD>90</TD></TR><TR><TD>Pipeline-Reconciliation_SAP</TD><TD>4</TD><TD>91</TD></TR><TR><TD>Costs-Reconciliation_MKT</TD><TD>17</TD><TD>93</TD></TR><TR><TD>Pipeline-Processing_SAP</TD><TD>23</TD><TD>88</TD></TR><TR><TD>Clients-Mining_SAP</TD><TD>15</TD><TD>74</TD></TR></TBODY></TABLE><P> </P><P> </P><P>I want to be able to go to all of the successful transactions per process name, and multiply the total Successful transaction per process name by the Job ROI TimeValueMin Baseline so it shows how much time TOTAL given back.. (For the successful part, I usually just put a page level/visual level filter instead of coding it in</P><P> </P><P>I hope this is somewhat clear. Is the SUMX formula doing just that? Is something the matter? Please assist if anyone has any insight.</P>Wed, 15 Sep 2021 21:50:40 GMTrzavgazaryan2021-09-15T21:50:40ZNeed to understand how/what is formula is doing | This Project was handed off to me | Please Help
https://community.powerbi.com/t5/Desktop/Need-to-understand-how-what-is-formula-is-doing-This-Project-was/m-p/2078634#M774008
<P>I am dealing with event log data - Successful/Failed Transaction Metrics. I want to calculate hours saved / ROI using an ROI Table. I will attach a PBIX File, Documentation, Dataset, ROI Static File. I want to understand what this formula is doing:</P><P> </P><DIV><DIV><SPAN>Hours Saved = SUMX(JobsDB, RELATED(jobsROI[timeValueMin]))/60</SPAN></DIV><DIV> </DIV><DIV><SPAN>Here is a sample of my fact table (JobsDB)</SPAN></DIV><DIV><SPAN><SPAN>jobIdjobStartjobEndjobStateprocessNameProcessing Time</SPAN></SPAN> <TABLE><TBODY><TR><TD>889</TD><TD>3/16/2019 12:12</TD><TD>3/16/2019 15:20</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.1</TD></TR><TR><TD>900</TD><TD>3/19/2019 8:00</TD><TD>3/19/2019 11:23</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.4</TD></TR><TR><TD>901</TD><TD>3/19/2019 12:18</TD><TD>3/19/2019 15:28</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.2</TD></TR><TR><TD>902</TD><TD>3/19/2019 16:27</TD><TD>3/19/2019 19:51</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.4</TD></TR><TR><TD>906</TD><TD>3/20/2019 23:34</TD><TD>3/21/2019 2:33</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.0</TD></TR><TR><TD>913</TD><TD>3/22/2019 9:33</TD><TD>3/22/2019 12:41</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.1</TD></TR><TR><TD>914</TD><TD>3/22/2019 14:08</TD><TD>3/22/2019 17:06</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.0</TD></TR><TR><TD>918</TD><TD>3/23/2019 8:00</TD><TD>3/23/2019 11:16</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.3</TD></TR><TR><TD>919</TD><TD>3/23/2019 11:49</TD><TD>3/23/2019 14:53</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.1</TD></TR><TR><TD>921</TD><TD>3/23/2019 20:28</TD><TD>3/23/2019 23:35</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.1</TD></TR><TR><TD>927</TD><TD>3/25/2019 15:24</TD><TD>3/25/2019 18:40</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.3</TD></TR><TR><TD>928</TD><TD>3/25/2019 18:52</TD><TD>3/25/2019 21:53</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.0</TD></TR><TR><TD>929</TD><TD>3/25/2019 23:29</TD><TD>3/26/2019 2:49</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.3</TD></TR><TR><TD>930</TD><TD>3/26/2019 8:00</TD><TD>3/26/2019 11:17</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.3</TD></TR><TR><TD>947</TD><TD>3/30/2019 16:35</TD><TD>3/30/2019 19:36</TD><TD>Successful</TD><TD>Logs-Reconciliation_SAP</TD><TD>3.0</TD></TR></TBODY></TABLE></DIV></DIV><P> </P><P>Here is the ROI Table that I'm pointing to in the SUMX equation:</P><P> </P><TABLE><TBODY><TR><TD>processName</TD><TD>timeValueMin</TD><TD>itemCost</TD></TR><TR><TD>Invoices-Processing_MKT</TD><TD>15</TD><TD>93</TD></TR><TR><TD>Pipeline-Analysis_SAP</TD><TD>30</TD><TD>76</TD></TR><TR><TD>Costs-Reconciliation_FIN</TD><TD>12</TD><TD>81</TD></TR><TR><TD>CustData-Reconciliation_SAP</TD><TD>24</TD><TD>82</TD></TR><TR><TD>Salesforce-Analysis_SAP</TD><TD>21</TD><TD>82</TD></TR><TR><TD>Salesforce-Processing_SAP</TD><TD>14</TD><TD>88</TD></TR><TR><TD>Clients-Reconciliation_SAP</TD><TD>18</TD><TD>85</TD></TR><TR><TD>Invoices-Reconciliation_MKT</TD><TD>7</TD><TD>72</TD></TR><TR><TD>Logs-Analysis_HR</TD><TD>7</TD><TD>69</TD></TR><TR><TD>Logs-Reconciliation_SAP</TD><TD>7</TD><TD>54</TD></TR><TR><TD>Invoices-Classification_FIN</TD><TD>29</TD><TD>54</TD></TR><TR><TD>CustData-Analysis_MKT</TD><TD>7</TD><TD>76</TD></TR><TR><TD>Logs-Classification_HR</TD><TD>27</TD><TD>99</TD></TR><TR><TD>Clients-Processing_MKT</TD><TD>16</TD><TD>90</TD></TR><TR><TD>Pipeline-Reconciliation_SAP</TD><TD>4</TD><TD>91</TD></TR><TR><TD>Costs-Reconciliation_MKT</TD><TD>17</TD><TD>93</TD></TR><TR><TD>Pipeline-Processing_SAP</TD><TD>23</TD><TD>88</TD></TR><TR><TD>Clients-Mining_SAP</TD><TD>15</TD><TD>74</TD></TR></TBODY></TABLE><P> </P><P> </P><P>I want to be able to go to all of the successful transactions per process name, and multiply the total Successful transaction per process name by the Job ROI TimeValueMin Baseline so it shows how much time TOTAL given back.. (For the successful part, I usually just put a page level/visual level filter instead of coding it in</P><P> </P><P>I hope this is somewhat clear. Is the SUMX formula doing just that? Is something the matter? Please assist if anyone has any insight.</P>Wed, 15 Sep 2021 21:50:40 GMThttps://community.powerbi.com/t5/Desktop/Need-to-understand-how-what-is-formula-is-doing-This-Project-was/m-p/2078634#M774008rzavgazaryan2021-09-15T21:50:40ZRe: Need to understand how/what is formula is doing | This Project was handed off to me | Please Hel
https://community.powerbi.com/t5/Desktop/Need-to-understand-how-what-is-formula-is-doing-This-Project-was/m-p/2078669#M774017
<P>Can you please post the link to the sample PBIX file?</P>Wed, 15 Sep 2021 22:32:32 GMThttps://community.powerbi.com/t5/Desktop/Need-to-understand-how-what-is-formula-is-doing-This-Project-was/m-p/2078669#M774017PaulDBrown2021-09-15T22:32:32ZRe: Need to understand how/what is formula is doing | This Project was handed off to me | Please Hel
https://community.powerbi.com/t5/Desktop/Need-to-understand-how-what-is-formula-is-doing-This-Project-was/m-p/2078700#M774028
<P><A href="https://marketplace.uipath.com/listings/power-bi-jobs-and-queues-dashboards/questions" target="_blank">https://marketplace.uipath.com/listings/power-bi-jobs-and-queues-dashboards/questions</A></P>Wed, 15 Sep 2021 22:58:29 GMThttps://community.powerbi.com/t5/Desktop/Need-to-understand-how-what-is-formula-is-doing-This-Project-was/m-p/2078700#M774028rzavgazaryan2021-09-15T22:58:29ZRe: Need to understand how/what is formula is doing | This Project was handed off to me | Please Hel
https://community.powerbi.com/t5/Desktop/Need-to-understand-how-what-is-formula-is-doing-This-Project-was/m-p/2085408#M775747
<P>Hi, <LI-USER uid="311468"></LI-USER> ;</P>
<P>1. The formula: </P>
<LI-CODE lang="markup">Hours Saved = SUMX(JobsDB, RELATED(jobsROI[timeValueMin]))/60</LI-CODE>
<P>is mean timeValueMin for each processName,<FONT color="#FF0000">"/60"</FONT> to convert this minute to hours; For example, in your table the logs-Reconciliation_sap corresponds to 7 minutes, and when convert to hours, 7/60=0.117 hours.</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="vyalanwumsft_0-1632121466535.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/596551i96F518350042069B/image-size/medium?v=v2&px=400" role="button" title="vyalanwumsft_0-1632121466535.png" alt="vyalanwumsft_0-1632121466535.png" /></span></P>
<P>2. Measure the total time.(if you filter the state is "successful")</P>
<LI-CODE lang="python">Total time =
VAR _sum =SUMX ( 'JobsDB', RELATED ( jobsROI[timeValueMin] ) ) / 60
RETURN
_sum
* CALCULATE (
COUNTROWS ( 'JobsDB' ),
FILTER ( ALLSELECTED ( 'JobsDB' ), [processName] = MAX ( [processName] ) ))</LI-CODE>
<P>The final output is shown below:</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="vyalanwumsft_1-1632121680045.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/596553i092832855F651427/image-size/medium?v=v2&px=400" role="button" title="vyalanwumsft_1-1632121680045.png" alt="vyalanwumsft_1-1632121680045.png" /></span></P>
<P>Best Regards,<BR />Community Support Team_ Yalan Wu<BR />If this post <EM><STRONG>helps</STRONG></EM>, then please consider <EM><STRONG>Accept it as the solution</STRONG></EM> to help the other members find it more quickly.</P>Mon, 20 Sep 2021 07:08:57 GMThttps://community.powerbi.com/t5/Desktop/Need-to-understand-how-what-is-formula-is-doing-This-Project-was/m-p/2085408#M775747v-yalanwu-msft2021-09-20T07:08:57Z