Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
jalaomar
Helper IV
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 

 

jalaomar_2-1698767651900.png

 

 

4 REPLIES 4
audreygerred
Super User
Super User

Hello! Try this: 

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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?

 

jalaomar_0-1698931044758.png

 

Try this:

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





No it's not working 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.