cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## 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 .

1 ACCEPTED SOLUTION
Regular Visitor

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 3
Frequent Visitor

Hello,

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

Thank you.

Regular Visitor

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])
Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors