Forum Discussion

james_pease's avatar
james_pease
Helper 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])
        )

    )

lbendlin's avatar
lbendlin
Super User
2 years ago

You are supplying filter conditions that always evaluate to true. Modify your filters so the actually have an effect.

 

The outer CALCULATE is likely not needed.

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523