Measure with IF renders useless results

[danielboi]

Hello everybody,

I am working on a model where I compare budget sales planning with actual sales.

The model looks like this (streamlined version)

In Fact Actual Sales I have measures to calculate the total revenue sum(Sales Revenue), volume sum(Sales Volume) and average price (Revenue / Volume), the fact budget sales table has same measures for the budget info.

For one formula I need to calcuate:

(Actual Price - Budget Price) * Actual Volume. This measure works perfectly. However, I need this calculation to be done only if Budget Price is 0.

This is where all the problem start. Run time very long and then I get a table (using excel right now) where all the attributes used (Customer, Material Number, Material Group, Currency) get copied down countless times and no values are shown. I tried various ways with defining variables before or having the separate calculation steps in separate measures (which on its own work fine!), but once it gets in an if statment it doesn't deliver expected result.

Thanks upfront

D

[AnkitKukreja]

Hi! @danielboi 

1) Create a function or custom column with condition, if Budget is 0, 'Yes', 'No'.
2) Then you can use your measure with filter condition only on where budget was 0.

I hope it helps. Please share some sample data with expected result so I can share the output.

[danielboi · Accepted solution]

Thank you Ankit. just around the time when you posted your suggestion I figured out that I had the order of my sumx and if formulas wrong. Before I put the sumx function into the then and else of the if statement. Actually needs to be the other way round.