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

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

      v-zhangti 

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

      Hi, Anonymous 

       

      I replaced the Measure you used for "GAP HORAS" with a calculated column.

      Column = IF([GAP HORAS]<0,0,1)

      Measure:

      Measure 2 = 
IF (
    HASONEVALUE ( 'data'[Column] ),
    MAX ( 'data'[Column]),
    SUM ( 'data'[Column] )
)

      Is this the result you expect?

       

      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 v-zhangti ,

         

        Your understanding is correct but unfortunately I couldn't replicate. I believe the reason is because the sample that I shared with you contains values and the actual .pbix file that I am using contains measures.

         

        With the example given in the sample before I tried to contextualize what I actually need: fix the "DM EX 50%" column, this column uses an IF condition (IF GAP Horas < 0, TRUE = 0, FALSE = Does a calculation) so the correct amount at total level in the print below is $ 1233,34 (sum of every row value):

         

         

        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 link for the file of the print above is here:

         

        https://mega.nz/file/kVwUWJ4b#RL4H-a9bOeWL0bf0WRZaGyw5lJc9yimYfueUPlfc-tY

         

        Thanks for your help.