SIMPLE DAX QUERY SEEMINGLY APPEARING LIKE QUANTUM MECHANICS

[safi]

Hello Everyone, 

Complete Noob to dax here, I am very confused and have been on the verge of quitting trying dax because I just cant wrap my head around how it works at the moment. Having said that, I refuse to throw in a towel and here I am. I am tyring to help someone build a powerbi dashboard and we are trying to model our data to where some basic calculations that are presently being done on an excel sheet can be replicated for our Powerbi model. 

Please consider to the snip below :
MOS@3M = This is months of supply at 3 Months, this a measure; the dax for which is as follows :

divide([qty stock incoming],[Consumption_3M_MonthlyAverage],999)

Lead Time: this is just a column from a dimensional table, column name is lead time months all of the values are '5' in it as in 5 months of lead time 

all i want powerbi to do is just do simple math, i.e = 5 minus 4.88 and the resulting value populated should show up in the last column (this is a column not a measure)
Months supply less leadtime @ 3M. 

the dax im currently using is to calculate is : 

MOS_Less_LeadTime_3M = if([MOS_3M]=999,999,  CALCULATE([MOS_3M]-AVERAGE('Dimension Item'[average lead time])))

this is only one iteration, i have used sumx, sum and what not, i just get wonky numbers and i dont know why? my understanding is that a sumx should infact work if i do a simple substraction formula but now that doesnt work, 

I am pulling my hair out because i feel at this point this so unintuitive that its discouraging me to not even proceed.

any help and any book reccomendation would be very helpful for me to not give up on this. 

also please know that I understand row context, sumx vs sum and how they work in theory but when  it comes to writing this expression I am completely lost. 

Pease help.

          

[safi]

@eugeneniemand @daxer-almighty @AntrikshSharma 

Hi, eugene consider anothe problem I am having ill share a table, and some snaps with you consider the table below 

The eventual goal of the table is to assess is to identify the items that can go out of stock and recommend a purchase quantity on 3 month, 6 month and 12 month windows. 
Using a 3 month window to test the, 

In order to do so, we need to calculate
-3 Month average consumption (consumption_3m measure and working fine)
-Average leadtime(Average lead time in the table image shared above), in our case this is a simple pull from dim table no formula needed
- Months of Supply at 3 Months : (MOS_3m) expression = 

MOS_3M = divide([qty stock incoming],[Consumption_3M_MonthlyAverage],999)
- Planned_MOS (planned mos of supply) this is the first challenge, the ouput I am getting that you see in the snap is a sum of sort, the expression is as follows (
Planned_MOS =

DIVIDE(SUM('Dimension Purchasing Inputs'[Turn Rate Target]),12,0)+sum('Dimension Purchasing Inputs'[Safety Stock Months ])  = the desired ouput should be 1.25+1 , 2.25 instead i am getting 43.75 dont know the issue with the dax expression but in the column i just shared the image of , it works as expected but not in a measure, row context issue but for now I dont know how to fix it.  thats help number 1 i need, 

-MOS_Less_Leadtime_3M_Measure this is the column we just fixed to ensure, the math is executed correctly, this is simply (average lead time - mos_3m) i.e. 7.8 minus 5= 2.8 
- baseline_qty_3M = this is our proposed quantity of item based on the months of supply adjusted for lead time, i.e  [Planned_MOS ]+ [safety Stock months]  times  [Consumption_3M_MonthlyAverage]  i.e 2*2.25 = 4.5

the expression for baseline_qt_3M is as follows : 
sumx('Dimension Item',
IF('Dimension Item'[Buy_Review_Status]= "Yes - Buy Review",
IF(SUMX('Dimension Item','Dimension Item'[MOS_Less_LeadTime_3M]*1)<=0,
(sumx('Dimension Purchasing Inputs',[Planned_MOS]+'Dimension Purchasing Inputs' [Safety Stock Months ])*([Consumption_3M_MonthlyAverage])),
sumx('Dimension Purchasing Inputs',[Planned_MOS]+'Dimension Purchasing Inputs'[Safety Stock Months ])-(sumx('Dimension Item','Dimension Item'[MOS_Less_LeadTime_3M]))),0)) the output at the moment is coming out to be 0, as opposed to 4.5

[daxer-almighty]

The is the book to understand all about DAX (and I mean ALL) : "The Definitive Guide to DAX" by Alberto Ferrari and Marco Russo. Now, since CALCULATE does context transition one has to be very careful when one uses this function in row contexts. Calculated columns are best calculated in Power Query or---if you can't for some reason---in DAX but WITHOUT CALCULATE. Because CALCULATE effects context transition it'll be agonizingly slow if you use it in a column in a big fact table (you can get away with small dimensions only). Sometimes to the point where it's not feasible to have it in there at all as the calculation does not return...

[AntrikshSharma]

@safi  Since you are calling a measure in the row context of calculated column you are getting incorrect numbers because of context transition.

[safi]

Thanks @AntrikshSharma that was the issue and it works now as expected. 

[eugeneniemand]

Can you share an example PBIX or at least some sample data and what you expect the output to be