Forum Discussion

ScORE's avatar
ScORE
Helper I
8 years ago
Solved

Subtotal/total help needed for Custom Column assistance!

                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

  • 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

3 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion

    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's avatar
      ScORE
      Helper I

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

       

      Thanks!

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee

    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]))