Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

how to calculate SUM of Increase and decrease and summary of all

Hello 

I have a table with amonth of order for each item (F[Value]) and the date of order (conceted to DIMDATE Table) , I needed to calulated sum of orders in selected month and it's prior month , so I did below measures :

 

 Total = SUM(F[Value])

Prior Month Forecast = CALCULATE([Total], PREVIOUSMONTH(DimDate[Date]))
 
and I have Incease and decresed and total changed 
Increase the Quantity =  VAR PrevMonthOrder = [Prior Month Forecast]
    RETURN
        IF(SUM(F[Value]) > PrevMonthOrder,CALCULATE( SUM(F[Value]) - PrevMonthOrder), BLANK())
 
Decrease the Quantity =    VAR PrevMonthOrder = [Prior Month Forecast]
    RETURN
        IF(SUM(F[Value]) < PrevMonthOrder,CALCULATE( SUM(F[Value]) - PrevMonthOrder), BLANK())
 
 
Forecast Quantity Changed = VAR PrevMonthOrder = [Prior Month Forecast]
    RETURN
 
        SUM(F[Value])- PrevMonthOrder

 in the abouve table for header there is not any sum because my measures calulated them separatly . 

 

Now , I need to calulate Sum of all increased and decreased and changes , I know that would be some row 

 

 

 


 all the increase and decreasing without considering the top headers.

I know that might be a row context calulation , but I dont know how I should apply it .

 
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    I found answere: 
    I have used this for decreasing : 

    SUMX(FILTER(ALLSELECTED('Forecasting Folder'[PartNo]),[Forecast Quantity Changed]<0),[Forecast Quantity Changed])
    and this one for increaseing : SUMX(FILTER(ALLSELECTED('Forecasting Folder'[PartNo]),[Forecast Quantity Changed]>0),[Forecast Quantity Changed])

3 Replies

  • nelsonwynn's avatar
    nelsonwynn
    Frequent Visitor
    3 years ago

    Hello,

    I also want to know answer about these question. I'm now occuring the same problem.

    Thank you.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I found answere: 
      I have used this for decreasing : 

      SUMX(FILTER(ALLSELECTED('Forecasting Folder'[PartNo]),[Forecast Quantity Changed]<0),[Forecast Quantity Changed])
      and this one for increaseing : SUMX(FILTER(ALLSELECTED('Forecasting Folder'[PartNo]),[Forecast Quantity Changed]>0),[Forecast Quantity Changed])
      • nelsonwynn's avatar
        nelsonwynn
        Frequent Visitor
        3 years ago

        Thank you so much. I'll try with your instruction.