Subtotal/total help needed for Custom Column assistance!

[ScORE]

                I have a table with several columns.  One of the columns is labeled RIR.  It is a calculated column that i created to calculate a rate based on data that is located in other columns.  I am using the Matrix Table and I need the total to reflect my calculated columns measure, not the sum, average, min, max count etc options that you are forced to use.  How can I get total & subtotal rows that use my actual measure?  The column that says Sun of RIR is a custom column made with the formula :

RIR = IFERROR('2017 Workbooks'[Total Rec Cases]*(200000/'2017 Workbooks'[Total Hours - Supervisory& Craft]),0.00)

I want this to used to calulate the subtotals and totals when the table is filtered, instaed of summing them.  The individual lines in the matrix (circled in green) are calulating the RIR correctly.  The Subtotals and totals hoghlighted in red are not.  I need to top line to do the following:

- Sum the Hours (this works)

- Sum the Cases (this works)

- Calulate the RIR according to my formula: RIR = IFERROR('2017 Workbooks'[Total Rec Cases]*(200000/'2017 Workbooks'[Total Hours - Supervisory& Craft]),0.00)  instead of Summing it.

I need Line 1 to read:

Total hours                total rec cases                                RIR

362,507                               3.0                                         1.66

The RIR is calculated : 3*(200,000/362507)= 1.66 

I need it to complete this calculation at the subtotal and total for each group, instaed of showing 79.46 (the sum).

Thanks!

Stephanie

[Phil_Seamark]

Hi @ScORE

Have you considered using a calculated measure?

Something like 

Calc as Measure = SUM('2017 Workbooks'[Total Rec Cases]) * (200000 / SUM('2017 Workbooks'[Total Hours - Supervisory& Craft]))

[Vvelarde · Accepted solution]

@ScORE

Hi, what happens if use a measure:

RIR =
IFERROR (
    SUM ( '2017 Workbooks'[Total Rec Cases] )
        * DIVIDE ( 200000, SUM ( '2017 Workbooks'[Total Hours - Supervisory& Craft] ) ),
    0.00
)

Let me know 

Regards

Victor

[ScORE]

That worked!  THank you.  I tried that, but I was getting an werror because I was utting the sum in the wrong place!

Thanks!