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

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 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @rzavgazaryan ;

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.

vyalanwumsft_0-1632121466535.png

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:

vyalanwumsft_1-1632121680045.png

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.

View solution in original post

3 REPLIES 3
v-yalanwu-msft
Community Support
Community Support

Hi, @rzavgazaryan ;

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.

vyalanwumsft_0-1632121466535.png

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:

vyalanwumsft_1-1632121680045.png

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.

View solution in original post

PaulDBrown
Super User
Super User

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.