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

 

  • 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.