Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
Solved! Go to 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
Hi @sanimesa,
Maybe you can take a look at following blog:
Optimizing DAX expressions involving multiple measures
Regards,
Xiaoxin Sheng
@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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
104 | |
102 | |
89 | |
66 |