cancel
Showing results for
Did you mean:
Helper I

## Sum of negative Sales.

In a Matrix visual the column shows
Sales,
MoM Diff ( Current month-Previous Month)
Neg Diff (Negative values in MoM Diff)

Main objective is to obtain the total for "Neg Diff" (columns total).
Sales, MoM Diff and Neg Diff are measures.

Required output shown below

Cheers!

11 REPLIES 11
Helper I

The data table is shown below,

The objective is to calculate the month over month sales difference and take sum of total negative sales difference for each month.

Required output is shown below,

I wasnot able to obtain this on Power BI, what I got is shown below, the total for MoM change and MoM -ve is the same.
Formulas used,

MoM Change = SUM('Sales'[Sales])- (CALCULATE(SUM('Sales'[Sales]), PREVIOUSMONTH('Calendar'[Date])))
MoM -ve = IF([MoM Change]<0,[MoM Change],BLANK())

I tired another formula with MoM -ve,

MoM -ve_2 = SUMX(VALUES(Sales[Customer]),[MoM -ve])
Results obtained,
Total -ve is partly correct, but it is not taking -ve monthly change wer current month sales is not there.

The main objective is to find total for negative month difference in sales.

Responsive Resident

@bharathsurya  Hey ,

Use below formula

Lt =
IF (
HASONEVALUE ( Sheet1[City] ),
SUM ( Sheet1[Sales] )
- CALCULATE (
SUM ( Sheet1[Sales] ),
DATESINPERIOD ( Sheet1[Date], MAX ( Sheet1[Date] ), -1, MONTH )
),
BLANK ()
)

then
Mom -ve = if(Hasonevalue(city),[mom],blank())

Kudos will appriciated

Community Champion

I think it will help to remove the date context when creating the list of customers.

``````MoM -ve_3 =
VAR Customers =
CALCULATETABLE ( VALUES ( Sales[Customer] ), ALLSELECTED ( 'Calendar' ) )
RETURN
SUMX ( Customers, [MoM Change] )``````
Helper I

It didnt work, output shown below.

Community Champion

I think I accidentally put the wrong measure inside the SUMX. Does it work with [Mom -ve] instead of [MoM Change]?

Helper I

Hi,
Thank you.
Its working fine when all months are selected. When only one month is selected it dsnt work. And even when two consecutive months are selected it works.
Can you find a solution for that too?

Community Champion

Solution Sage

``````// Something like this...

[Neg MoM] =
var negSum =
SUMX(
Customer, // the Customer dimension
var diff = [MoM Diff]
return
( diff < 0 ) * diff
)
return
if( negSum, negSum )``````

Helper I

It didnt work.

Solution Sage

I can't tell you more since you don't give me more information and I don't know your model. My guess was the best I could do. In my model it does work.

Helper I

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors