Forum Discussion
Subtotal miscaculation using an IF function
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
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