topic Division Based on Condition/Filter in DAX Commands and Tips

Division Based on Condition/Filter

james_pease — Tue, 21 May 2024 16:36:27 GMT

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!

Re: Division Based on Condition/Filter

lbendlin — Wed, 22 May 2024 01:02:31 GMT

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

Re: Division Based on Condition/Filter

james_pease — Wed, 22 May 2024 15:29:36 GMT

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])

)

Re: Division Based on Condition/Filter

lbendlin — Wed, 22 May 2024 15:56:06 GMT

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