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