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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Please help with ROI Calculation SUMX - Am I understanding this correctly? Is it doing what I need?

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-yiruan-msft
Community Support
Community Support

Hi @Anonymous ,

You can create two measures as below to get it:

 

Measure = 
VAR _countoftransactions =
    CALCULATE (
        DISTINCTCOUNT ( 'JobsDB'[jobId] ),
        FILTER (
            'JobsDB',
            'JobsDB'[processName] = SELECTEDVALUE ( 'JobsDB'[processName] )
        )
    )
RETURN
    DIVIDE (
        _countoftransactions * SELECTEDVALUE ( 'jobsROI'[timeValueMin] ),
        60,
        0
    )
Hours Saved = SUMX(VALUES('jobsROI'[processName]),[Measure])

 

yingyinr_0-1632131324251.png

Besides that, you can create a calculated column in table jobsROI as below:

 

Column = 
CALCULATE (
    DISTINCTCOUNT ( 'JobsDB'[jobId] ),
    FILTER ( 'JobsDB', 'JobsDB'[processName] = EARLIER ( 'jobsROI'[processName] ) )
) * 'jobsROI'[timeValueMin] / 60

 

yingyinr_1-1632131734163.png

Please find the details in the attachment.

Best Regards

Community Support Team _ Rena
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

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

Hi @Anonymous ,

You can create two measures as below to get it:

 

Measure = 
VAR _countoftransactions =
    CALCULATE (
        DISTINCTCOUNT ( 'JobsDB'[jobId] ),
        FILTER (
            'JobsDB',
            'JobsDB'[processName] = SELECTEDVALUE ( 'JobsDB'[processName] )
        )
    )
RETURN
    DIVIDE (
        _countoftransactions * SELECTEDVALUE ( 'jobsROI'[timeValueMin] ),
        60,
        0
    )
Hours Saved = SUMX(VALUES('jobsROI'[processName]),[Measure])

 

yingyinr_0-1632131324251.png

Besides that, you can create a calculated column in table jobsROI as below:

 

Column = 
CALCULATE (
    DISTINCTCOUNT ( 'JobsDB'[jobId] ),
    FILTER ( 'JobsDB', 'JobsDB'[processName] = EARLIER ( 'jobsROI'[processName] ) )
) * 'jobsROI'[timeValueMin] / 60

 

yingyinr_1-1632131734163.png

Please find the details in the attachment.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lbendlin
Super User
Super User

The SUMX calculates the summary ROI in hours rather than in minutes.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors