Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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.
Solved! Go to Solution.
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])
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
Please find the details in the attachment.
Best Regards
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])
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
Please find the details in the attachment.
Best Regards
The SUMX calculates the summary ROI in hours rather than in minutes.
User | Count |
---|---|
16 | |
11 | |
5 | |
4 | |
3 |