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 ( 'Dev projects'[Total Cost] ), 'All Actual Data'[Store Impacted])
    ),
    StoreList[StoreNumber]
)
 

 

What I am trying to do is divide by the total cost for the Store Impacted hence the calculate filter by [Store Impacted]. I do not want to divide by the [Total Cost] for a specific Store, I want to divide the profit by the store being impacted [Total Cost]. The Store Impacted column is a calculated column in the 'All Actual Data' table which is a lookupvalue formula pulling the actual column from the 'ROI_Impact_Stores' table. An unnecessary step as their is a relationship between the two tables, just providing more context.

For example, in the image above, I want to divide the monthly profit excess measure by the cost for the test store, 13348 (this is the Store Impacted) for each control store and the test store.
So monthly profit for 13268 / cost for store 13348. 
 
Thank you in advance!
  • 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])
        )

    )

3 Replies

  • 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

  • james_pease's avatar
    james_pease
    Helper 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
      Super User
      2 years ago

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