Need to understand how/what is formula is doing | This Project was handed off to me | Please Help

[Anonymous]

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:

Hours Saved = SUMX(JobsDB, RELATED(jobsROI[timeValueMin]))/60

Here is a sample of my fact table (JobsDB)

jobIdjobStartjobEndjobStateprocessNameProcessing Time

889	3/16/2019 12:12	3/16/2019 15:20	Successful	Logs-Reconciliation_SAP	3.1
900	3/19/2019 8:00	3/19/2019 11:23	Successful	Logs-Reconciliation_SAP	3.4
901	3/19/2019 12:18	3/19/2019 15:28	Successful	Logs-Reconciliation_SAP	3.2
902	3/19/2019 16:27	3/19/2019 19:51	Successful	Logs-Reconciliation_SAP	3.4
906	3/20/2019 23:34	3/21/2019 2:33	Successful	Logs-Reconciliation_SAP	3.0
913	3/22/2019 9:33	3/22/2019 12:41	Successful	Logs-Reconciliation_SAP	3.1
914	3/22/2019 14:08	3/22/2019 17:06	Successful	Logs-Reconciliation_SAP	3.0
918	3/23/2019 8:00	3/23/2019 11:16	Successful	Logs-Reconciliation_SAP	3.3
919	3/23/2019 11:49	3/23/2019 14:53	Successful	Logs-Reconciliation_SAP	3.1
921	3/23/2019 20:28	3/23/2019 23:35	Successful	Logs-Reconciliation_SAP	3.1
927	3/25/2019 15:24	3/25/2019 18:40	Successful	Logs-Reconciliation_SAP	3.3
928	3/25/2019 18:52	3/25/2019 21:53	Successful	Logs-Reconciliation_SAP	3.0
929	3/25/2019 23:29	3/26/2019 2:49	Successful	Logs-Reconciliation_SAP	3.3
930	3/26/2019 8:00	3/26/2019 11:17	Successful	Logs-Reconciliation_SAP	3.3
947	3/30/2019 16:35	3/30/2019 19:36	Successful	Logs-Reconciliation_SAP	3.0

Here is the ROI Table that I'm pointing to in the SUMX equation:

processName	timeValueMin	itemCost
Invoices-Processing_MKT	15	93
Pipeline-Analysis_SAP	30	76
Costs-Reconciliation_FIN	12	81
CustData-Reconciliation_SAP	24	82
Salesforce-Analysis_SAP	21	82
Salesforce-Processing_SAP	14	88
Clients-Reconciliation_SAP	18	85
Invoices-Reconciliation_MKT	7	72
Logs-Analysis_HR	7	69
Logs-Reconciliation_SAP	7	54
Invoices-Classification_FIN	29	54
CustData-Analysis_MKT	7	76
Logs-Classification_HR	27	99
Clients-Processing_MKT	16	90
Pipeline-Reconciliation_SAP	4	91
Costs-Reconciliation_MKT	17	93
Pipeline-Processing_SAP	23	88
Clients-Mining_SAP	15	74

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

I hope this is somewhat clear. Is the SUMX formula doing just that? Is something the matter? Please assist if anyone has any insight.

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

1. The formula: 

Hours Saved = SUMX(JobsDB, RELATED(jobsROI[timeValueMin]))/60

is mean timeValueMin for each processName,"/60" 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.

2. Measure the total time.(if you filter the state is "successful")

Total time =
VAR _sum =SUMX ( 'JobsDB', RELATED ( jobsROI[timeValueMin] ) ) / 60
RETURN
    _sum
        * CALCULATE (
            COUNTROWS ( 'JobsDB' ),
            FILTER ( ALLSELECTED ( 'JobsDB' ), [processName] = MAX ( [processName] ) ))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

Can you please post the link to the sample PBIX file?

[Anonymous]

https://marketplace.uipath.com/listings/power-bi-jobs-and-queues-dashboards/questions