Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.