cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper IV

## DAX measure margin calculation

Dear all,

I have a simple measure to calculate Forecast Margin% and all is working well except that it return 1 when the forecast cost is missing

how can I ensure that when forecast is 0 then return 0% when calculating Forecast Margin% (FM%)

Forecast Margin = Total Sales - Total Forecast Cost

Forecast Margin% = Forecast Margin / Total Sales

4 REPLIES 4
Super User

Hello! Try this:

#fm% =
IF(
[ForecastCost] = 0,
0,
DIVIDE([#ForecastMargin2], [#TotalSales])
)

Proud to be a Super User!

Helper IV

It's kinda working but still not 100%

it does what it's supposed to but when I add this in a table it shows all projects were there is no cost as well regardless om filtering. How can I optimize the dax measure?

Super User

Try this:

Margin =
CALCULATE(
DIVIDE([#ForecastMargin2], [#TotalSales]),
FILTER(
ALL('YourTable'),
[BudgetCost] > 0 && [ForecastCost] > 0 ||
[BudgetCost] > 0 && [ForecastCost] = 0
)
)

Proud to be a Super User!

Helper IV

No it's not working