Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Subtotal miscaculation using an IF function

Hello,

 

I have been strugglin to solve the situation with the formula below:

 

DM EX 50% =

IF([GAP HORAS]<0,0,

((SUM('PLANNED HOURS'[MAINTENANCE BUDGET]) - SUM('WORK_ORDER_CODING_UIV'[MAINTENANCE COST]) - SUM('PLANNED HOURS'[MAINTENANCE BUDGET DM]))*0.5))

 

 

The thing is that at row level the calculations are okay, but when I see the subtotal, it is still considering the "GAP HORAS" value at that level (subtotal) and redoing the calculus, instead of adding every row value above.

 

Any ideas about how to solve this?

 

Thanks in advance.

 

13 Replies

    • amitchandak's avatar
      amitchandak
      Super User
      4 years ago

      Anonymous , You need force row context using all matrix rows . This is an example using correct fact and dimension in summarize you need to do that

       


      sumx(ADDCOLUMNS( summarize( 'WORK_ORDER_CODING_UIV', 'WORK_ORDER_CODING_UIV'[Contract], 'WORK_ORDER_CODING_UIV'[Year]), "_1",
      calculate(IF([GAP HORAS]<0,0,
      ((SUM('PLANNED HOURS'[MAINTENANCE BUDGET]) - SUM('WORK_ORDER_CODING_UIV'[MAINTENANCE COST]) - SUM('PLANNED HOURS'[MAINTENANCE BUDGET DM]))*0.5)))), [_1])

       

      why my grand total is wrong -https://www.youtube.com/watch?v=ufHOOLdi_jk

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hello amitchandak , thanks for your time and reply.

         

        1. I found out this as a possible solution, the thing is that I couldn't been able to use it effectively.

         

        MEASURE = SUMX(ADDCOLUMNS(SUMMARIZE
        (WORK_ORDER_CODING_UIV, 'CONTRACT'[CONTRACT], 'CALENDAR'[PERIOD], FLEET[MODEL],WORK_ORDER_CODING_UIV[QD. TAG],FLEET[TAG],'CALENDAR'[DATE]), "_1",
        CALCULATE(IF([GAP HORAS]<0,0,
        ((SUM('PLANNED HOURS'[MAINTENANCE BUDGET]) - SUM('WORK_ORDER_CODING_UIV'[MAINTENANCE COST]) - SUM('PLANNED HOURS'[MAINTENANCE BUDGET DM]))*0.5)))), [_1])
         

         

        1. Youtube solution:
        I tried to contextualize and see how it fits, but the result was the same as in the post (result per row okay, per total not).
         
        T. DM EX 50% = IF(HASONEVALUE('PLANNED HOURS'[QD. TAG]),[DM EX 50%],
        SUMX(
        VALUES(
        'PLANNED HOURS'[QD. TAG]),[DM EX 50%]))
         

         

  • jameszhang0805's avatar
    jameszhang0805
    Resolver IV
    4 years ago

    Anonymous  I am not sure if I understand it correctly. It looks like this is a common subtotals issue. 

    If the following is the result you want to return, then use my code. Thanks.

    Measure = 
SUMX (
    SUMMARIZE ( data, 'data'[PERIOD], 'data'[TAG], 'data'[QD. TAG], 'data'[DATE] ),
    CALCULATE ( IF ( SUM ( data[GAP HORAS] ) < 0, 0, 1 ) )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello jameszhang0805 

       

      I'm sorry but I still couldn't do what I need:

       

      The column [GAP HORAS] is a "bonus prize", so I have a formula that has an IF Conditional. The thing is that I need to sum the values at subtotal level [GAP HORAS] > 0 (goal achieved)

      If [GAP HORAS] at subtotal level is < 0, result is 0 or blank.

       

      The thing is that the IF function is also working at TOTAL level, so regardless the value of each SUBTOTAL, if the GAP HORAS is < 0 at TOTAL level, the result is 0 (not what I expect). See examples:

       

      For a subtotal with GAP HORAS < 0. Expected result = 0 (regardless the values of each row above).

       

       

      The formula that I am using is:

       

      DM EX 50% =
      IF([GAP HORAS]<0,BLANK(),
      ((SUM('PLANNED HOURS'[MAINTENANCE BUDGET]) - SUM('WORK_ORDER_CODING_UIV'[MAINTENANCE COST]) - SUM('PLANNED HOURS'[MAINTENANCE BUDGET DM]))*0.5))
       
      Reminding that [GAP HORAS] is also a measure.