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

Dax formula for sum wiith filtres

Hello!

 

I need the amount (sum) by name without a negative amount (sum) or a without positive amount (sum). 

For example:

№ name Value
1 Com1 200
2 Com1 300
3 Com2 500
4 Com2 -200
5 Com3 -500
6 Com3 300

 Result:

//Without negative value
№ name Value
1 Com1 500
2 Com2 300

//Without positive value
№ name Value
1 Com3 -200

Thanks for all! 

3 REPLIES 3
TheOnlyBob_2
Frequent Visitor

Hi,

 

This should be - 

 

positivevalues = calculate(sum([value]),filter(tablename,[value]>0))

 

negativevalues = calculate(sum([value]),filter(tablename,[value]<0))

 

This can also be achieved with the visual level filters, unless you need to show both on the same visual.

 

Rob

Phil_Seamark
Employee
Employee

Hi @Anonymous

 

You could try two calculated measures

 

Positive = 
VAR x =SUM(Table1[VAlue])
RETURN IF(x>=0,x)

and

 

Negative = 
VAR x =SUM(Table1[VAlue])
RETURN IF(x<0,x)

 

 

If you drag these measures to their own visuals along with the [name] field you get the following.

 

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

Hi @Phil_Seamark

 

It's good idea, sorry but I'm using formula for calculate like this: 

var corrected = 
IF(MIN('Calendar'[Date]) <= CALCULATE(MAX('Table'[Date]), ALL('Table')), 
   CALCULATE(SUM('Table'[Value]),
      FILTER(ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date])))) 
return 
//other formula + corrected

 

so, I need measure with that formula. Thanks for all!

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.