Need help in understanding measure in a table and filters applied

[sanimesa]

Please review the below screenshots, I am trying to understand why formula 2 for contribution margin works as expected while formula 1 does not. The formula 1 seems to mess up the table and does not honor the slicer on the invoice year.  Any pointers, reference will be helpful. All the visuals in the table visual are coming from the same table or measures on the same table. 

For reference, the two formula are:

Formula 1: 

Contribution Margin % = 1 - DIVIDE([Total Cost], [Total Sales]) 

Formula 2: 

Contribution Margin 2 % =
VAR margin = DIVIDE([Total Cost], [Total Sales], BLANK())
RETURN IF(ISBLANK(margin), BLANK(), 1 - margin)

The formula for Total Sales and Total Cost measures:

Total Sales = SUM('Invoice Details'[ExtPrice]) 

Total Cost = SUM('Invoice Details'[ExtCost]) 

Formula 1: messes up the table, slicer not honeredFormula 2: works as expected

[v-shex-msft]

Hi @sanimesa,

Maybe you can take a look at following blog:

Optimizing DAX expressions involving multiple measures

Regards,

Xiaoxin Sheng

[sanimesa]

@v-shex-msft Thanks. But why is the filter not being reflected in the first formula? What happens when a measure simply returns a scalar value (say 1)? I suppose I am not getting the logic of how adding a measure to a table visual might impact the table. 

[v-shex-msft · Accepted solution]

HI @sanimesa,

Your first formula has static value 1, when right part formula not have valid result, it still use static value and blank to calculate.

I guess power bi will recognize blank as zero when calculating with numeric value, so it should be '1-blank= 1-0=1'.

I think this is the reason why you can add +0 after measure to display all records.(normally power bi will auto hide blank records)

Regards,

Xiaoxin Shen