cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rzavgazaryan
Helper III
Helper III

Need help with ROI Calculation with SUMX - AM I understanding correctly?

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

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
8893/16/2019 12:123/16/2019 15:20SuccessfulLogs-Reconciliation_SAP3.1
9003/19/2019 8:003/19/2019 11:23SuccessfulLogs-Reconciliation_SAP3.4
9013/19/2019 12:183/19/2019 15:28SuccessfulLogs-Reconciliation_SAP3.2
9023/19/2019 16:273/19/2019 19:51SuccessfulLogs-Reconciliation_SAP3.4
9063/20/2019 23:343/21/2019 2:33SuccessfulLogs-Reconciliation_SAP3.0
9133/22/2019 9:333/22/2019 12:41SuccessfulLogs-Reconciliation_SAP3.1
9143/22/2019 14:083/22/2019 17:06SuccessfulLogs-Reconciliation_SAP3.0
9183/23/2019 8:003/23/2019 11:16SuccessfulLogs-Reconciliation_SAP3.3
9193/23/2019 11:493/23/2019 14:53SuccessfulLogs-Reconciliation_SAP3.1
9213/23/2019 20:283/23/2019 23:35SuccessfulLogs-Reconciliation_SAP3.1
9273/25/2019 15:243/25/2019 18:40SuccessfulLogs-Reconciliation_SAP3.3
9283/25/2019 18:523/25/2019 21:53SuccessfulLogs-Reconciliation_SAP3.0
9293/25/2019 23:293/26/2019 2:49SuccessfulLogs-Reconciliation_SAP3.3
9303/26/2019 8:003/26/2019 11:17SuccessfulLogs-Reconciliation_SAP3.3
9473/30/2019 16:353/30/2019 19:36SuccessfulLogs-Reconciliation_SAP3.0

 

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

 

processNametimeValueMinitemCost
Invoices-Processing_MKT1593
Pipeline-Analysis_SAP3076
Costs-Reconciliation_FIN1281
CustData-Reconciliation_SAP2482
Salesforce-Analysis_SAP2182
Salesforce-Processing_SAP1488
Clients-Reconciliation_SAP1885
Invoices-Reconciliation_MKT772
Logs-Analysis_HR769
Logs-Reconciliation_SAP754
Invoices-Classification_FIN2954
CustData-Analysis_MKT776
Logs-Classification_HR2799
Clients-Processing_MKT1690
Pipeline-Reconciliation_SAP491
Costs-Reconciliation_MKT1793
Pipeline-Processing_SAP2388
Clients-Mining_SAP1574

 

 

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.

1 REPLY 1
daxer-almighty
Solution Sage
Solution Sage

The thread started on Wednesday. Today is Saturday. No reply. That means people don't understand your problem. You could/should rephrase it. And please make sure your explanation of the issue, tables and pictures are as clear as the Sun.

 

How to Get Your Question Answered Quickly - Microsoft Power BI Community

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!