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'[MAINTENA...
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%]))
 

 

amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous , Try like

IF(HASONEVALUE('PLANNED HOURS'[QD. TAG]),
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))
,
((SUM('PLANNED HOURS'[MAINTENANCE BUDGET]) - SUM('WORK_ORDER_CODING_UIV'[MAINTENANCE COST]) - SUM('PLANNED HOURS'[MAINTENANCE BUDGET DM]))*0.5)
)

 

or you have use summarize to get more than one column to group

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    amitchandak In that case is doing the calculation at subtotal level:

     

     

    • v-zhangti's avatar
      v-zhangti
      Community Support
      4 years ago

      Hi, Anonymous 

       

      If the above response does not solve your problem. Can you provide easy to use PBIX files for testing? What kind of results do you expect? You can mark it in the screenshot. Looking forward to your reply.

       

      Best Regards,

      Community Support Team _Charlotte

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hello,

         

        Below is the link to download the PBI Sample, as requested (couldn't find a way to attach it here):

         

        https://mega.nz/file/EZoFma5L#IzVrFTbbQjb0mKfktiu3Nc4GYOEIJ1Z6Avvja9sQkRM

         

        I have a column called "GAP HORAS" and I created a measure that uses this column with the following expression:

         

        Measure = IF(SUM(data[GAP HORAS])<0,0,1)
         
        What happens is that per row level, the formula is okay, but at subtotal level it is not. For example the correct answer for half of the matrix would be 8 instead of 0 (the sum of each individual row instead of recalculate the value at subtotal/total level.)


         

        Thanks in advance.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      amitchandak