Forum Discussion
Subtotal miscaculation using an IF function
Anonymous I am not sure if I understand it correctly. It looks like this is a common subtotals issue.
If the following is the result you want to return, then use my code. Thanks.
Measure =
SUMX (
SUMMARIZE ( data, 'data'[PERIOD], 'data'[TAG], 'data'[QD. TAG], 'data'[DATE] ),
CALCULATE ( IF ( SUM ( data[GAP HORAS] ) < 0, 0, 1 ) )
)Hello jameszhang0805
I'm sorry but I still couldn't do what I need:
The column [GAP HORAS] is a "bonus prize", so I have a formula that has an IF Conditional. The thing is that I need to sum the values at subtotal level [GAP HORAS] > 0 (goal achieved)
If [GAP HORAS] at subtotal level is < 0, result is 0 or blank.
The thing is that the IF function is also working at TOTAL level, so regardless the value of each SUBTOTAL, if the GAP HORAS is < 0 at TOTAL level, the result is 0 (not what I expect). See examples:
For a subtotal with GAP HORAS < 0. Expected result = 0 (regardless the values of each row above).
The formula that I am using is:
DM EX 50% =IF([GAP HORAS]<0,BLANK(),((SUM('PLANNED HOURS'[MAINTENANCE BUDGET]) - SUM('WORK_ORDER_CODING_UIV'[MAINTENANCE COST]) - SUM('PLANNED HOURS'[MAINTENANCE BUDGET DM]))*0.5))Reminding that [GAP HORAS] is also a measure.