Forum Discussion

cocoloco79's avatar
cocoloco79
Icon for Helper III rankHelper III
4 years ago
Solved

Incorrect value used from IF(ISBLANK calculation

Hi everyone,

 

I hope someone can help me with this. I cannot work out why formula 1 is picking up the right value '8000' but in the next calculation step in formula 2 the default value is picket up as '6900' and the Est. Tips/Planting results in 16.51 insead of '14.23'.

 

Can anyone help with this?

 

Est Cobs Planting= 113909

BI_Planting[Est. Pieces/Tip = 8000

Deafult_Corn Tips= 6900

Desired Result= 14.23

 

Formula 1 - This formula should pick Est. Pieces/Tip which is 8000 correctly

(Corn) Tip Default = IF(ISBLANK(BI_Planting[Est. Pieces/Tip]), MIN(Default_Corn[Tips]), BI_Planting[Est. Pieces/Tip])
 
Formula 2 - This formula should calculate how many Tips I get out of 113909 cobs. However it is looking at the default value of 6900 cobs and not 8000 cobs which is incorrect
(Corn) Est. Tips NOSUM =
DIVIDE(
    SUM('BI_Planting'[(Corn) Est. Cobs/ Planting]),
    SUM('Default_Corn'[Tips])
)
 
Formula 3 - This formula is summarising the tips
(Corn) Est. Tips/ Planting = SUMX(SUMMARIZE(BI_Planting,BI_Planting[PlantingName],BI_Planting[PlantingCode],"value",BI_Planting[(Corn) Est. Tips NOSUM]),[Value])
 
 
 
Need Help
  • cocoloco79's avatar
    cocoloco79
    4 years ago

    Sorry for not making myself clear enough.

    Deafult_Corn Tips= 6900 should be ignored if a value exist for BI_Planting[Est. Pieces/Tip = 8000

     

    I was able to reslove this by using this formula:

    Formula 1 = 

    (Corn) Est.Tips/Planting No Sum =
    DIVIDE(
        SUM('BI_Planting'[(Corn) Est. Cobs/ Planting]),
        SUM('BI_Planting'[(Corn) Tip Default])
     
    Formula 2 =
    (Corn) Est. Tips/ Planting = SUMX(SUMMARIZE(BI_Planting,BI_Planting[PlantingName],BI_Planting[PlantingCode],"value",BI_Planting[(Corn) Est.Tips/Planting No Sum]),[Value])

2 Replies

  • NickolajJessen's avatar
    NickolajJessen
    Icon for Solution Sage rankSolution Sage
    4 years ago

    In measure 2 you are using SUM('Default_Corn'[Tips]) as the denominator - which is 6900. Why are you confused when 6900 show up?

  • cocoloco79's avatar
    cocoloco79
    Icon for Helper III rankHelper III
    4 years ago

    Sorry for not making myself clear enough.

    Deafult_Corn Tips= 6900 should be ignored if a value exist for BI_Planting[Est. Pieces/Tip = 8000

     

    I was able to reslove this by using this formula:

    Formula 1 = 

    (Corn) Est.Tips/Planting No Sum =
    DIVIDE(
        SUM('BI_Planting'[(Corn) Est. Cobs/ Planting]),
        SUM('BI_Planting'[(Corn) Tip Default])
     
    Formula 2 =
    (Corn) Est. Tips/ Planting = SUMX(SUMMARIZE(BI_Planting,BI_Planting[PlantingName],BI_Planting[PlantingCode],"value",BI_Planting[(Corn) Est.Tips/Planting No Sum]),[Value])