Forum Discussion
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...
Hello amitchandak , thanks for your time and reply.
- 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])
- 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%]))
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