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...
v-zhangti's avatar
v-zhangti
Community Support
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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.