Subtotal miscaculation using an IF function

[Anonymous]

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'[MAINTENANCE COST]) - SUM('PLANNED HOURS'[MAINTENANCE BUDGET DM]))*0.5))

 

The thing is that at row level the calculations are okay, but when I see the subtotal, it is still considering the "GAP HORAS" value at that level (subtotal) and redoing the calculus, instead of adding every row value above.

Any ideas about how to solve this?

Thanks in advance.

[jameszhang0805]

@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 ) )
)

[Anonymous]

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.

[Anonymous]

@VahidDM @AlexisOlson @smpa01 😁

[amitchandak]

@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]

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]

@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]

@amitchandak In that case is doing the calculation at subtotal level:

 

[v-zhangti]

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.

[Anonymous]

Hello,

Below is the link to download the PBI Sample, as requested (couldn't find a way to attach it here):

https://mega.nz/file/EZoFma5L#IzVrFTbbQjb0mKfktiu3Nc4GYOEIJ1Z6Avvja9sQkRM

I have a column called "GAP HORAS" and I created a measure that uses this column with the following expression:

Measure = IF(SUM(data[GAP HORAS])<0,0,1)

What happens is that per row level, the formula is okay, but at subtotal level it is not. For example the correct answer for half of the matrix would be 8 instead of 0 (the sum of each individual row instead of recalculate the value at subtotal/total level.)

Thanks in advance.

[v-zhangti]

Hi, @Anonymous 

I replaced the Measure you used for "GAP HORAS" with a calculated column.

Column = IF([GAP HORAS]<0,0,1)

Measure:

Measure 2 = 
IF (
    HASONEVALUE ( 'data'[Column] ),
    MAX ( 'data'[Column]),
    SUM ( 'data'[Column] )
)

Is this the result you expect?

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.

[Anonymous]

Hello @v-zhangti ,

Your understanding is correct but unfortunately I couldn't replicate. I believe the reason is because the sample that I shared with you contains values and the actual .pbix file that I am using contains measures.

With the example given in the sample before I tried to contextualize what I actually need: fix the "DM EX 50%" column, this column uses an IF condition (IF GAP Horas < 0, TRUE = 0, FALSE = Does a calculation) so the correct amount at total level in the print below is $ 1233,34 (sum of every row value):

DM EX 50% =

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

The link for the file of the print above is here:

https://mega.nz/file/kVwUWJ4b#RL4H-a9bOeWL0bf0WRZaGyw5lJc9yimYfueUPlfc-tY

Thanks for your help.

[Anonymous]

@v-zhangti 

[Anonymous]

@amitchandak