Forum Discussion

jalaomar's avatar
jalaomar
Helper IV
2 years ago

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 

 

 

 

Need Help

4 Replies

  • audreygerred's avatar
    audreygerred
    Super User
    2 years ago

    Hello! Try this: 

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

    • jalaomar's avatar
      jalaomar
      Helper IV
      2 years ago

      Hi audreygerred 

      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?

       

       

      • audreygerred's avatar
        audreygerred
        Super User
        2 years ago

        Try this:

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