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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

bharathsurya_0-1618427161098.png

 

 

 

Thanks in advance.
Cheers!

 





11 REPLIES 11
Anonymous
Not applicable

 

The data table is shown below,

bharathsurya_2-1618501207727.png

 

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,

bharathsurya_3-1618501515913.png


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())

 

bharathsurya_4-1618501707740.png

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.
bharathsurya_5-1618503822227.png

Please help with a solution.
The main objective is to find total for negative month difference in sales.
Thanks in advance.

 

HarishKM
Impactful Individual
Impactful Individual

@Anonymous  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 

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] )
Anonymous
Not applicable

It didnt work, output shown below.

bharathsurya_0-1618512534593.png

 

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

Anonymous
Not applicable

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?

Try ALL instead of ALLSELECTED.

Anonymous
Not applicable

 

// Something like this...
// Adjust if needed.

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

 

Anonymous
Not applicable

It didnt work.

Anonymous
Not applicable

@Anonymous 

 

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.

Anonymous
Not applicable

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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