Forum Discussion

james_pease's avatar
james_pease
Icon for Helper III rankHelper III
2 years ago
Solved

Division Based on Condition/Filter

Good morning, I am using the current formula in a measure: Annual Impacted True ROI = CALCULATE (     DIVIDE (         ( [Monthly Profit Difference (excess)] * 12 ),         Calculate(SUM...
  • james_pease's avatar
    james_pease
    2 years ago

    Thank you! This helped me come up with an alternative solution. I created a lookupvalue column (just so I can visually see):

     

    Total Cost Store Impacted = LOOKUPVALUE('Dev projects'[Total Cost], 'Dev projects'[Store], ROI_Impact_Stores[Store Impacted])
    Then I adjusted the formula for the annual true ROI measure:

    Annual Impacted True ROI =
        DIVIDE (
            ( [Monthly Profit Difference (excess)] * 12 ),
             CALCULATE(sum('ROI_Impact_Stores'[Total Cost Store Impacted])
        )

    )

james_pease's avatar
james_pease
Icon for Helper III rankHelper III
2 years ago

Thank you! This helped me come up with an alternative solution. I created a lookupvalue column (just so I can visually see):

 

Total Cost Store Impacted = LOOKUPVALUE('Dev projects'[Total Cost], 'Dev projects'[Store], ROI_Impact_Stores[Store Impacted])
Then I adjusted the formula for the annual true ROI measure:

Annual Impacted True ROI =
    DIVIDE (
        ( [Monthly Profit Difference (excess)] * 12 ),
         CALCULATE(sum('ROI_Impact_Stores'[Total Cost Store Impacted])
    )

)

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

Ok, now find a solution that does not use LOOKUPVALUE...