Eleminate negative numbers in total from calculation

[RoyB1973]

Hi All,

I'm trying to make a measure which eleminates the negative numbers.

Case:

3 datasets:

PMExtraTech which contains articles and the quantity.

PMAbonne which contains info about the end customer

Customer which contact info about our customers

For a special promotion our customers can earn points for gifts. Therefore I need to see how many growth a customer has between two periods.  The current period is updated every 2 hours with the latest information.

So I made a calculation which gives me the growth. When I make a table with the information, The first colomn is the CustomerId the Second one the growth and the third the earnt points. However you can't earn negative points. Untill sofar it works fine.

So 10 points per 1 qty. But you see that the total in both columns are the same. So in the total the negative numbers are also calculated? But they are not shown in the points column.

Relations:

PMExtraTech has and Connection with PMAbonnee

and PMAbonnee has a Connection with Customer

Used Calculation for the points:

If have also tried a SumX with a Summarize on customer, but this is giving a more mixed up info.

When I removed the customer column and just changed the ( IF(CalCulation)<0,1,2) Then its gving a 2 as responce, so I think that is the reason why you don't see the negative numbers on customer, but they are counted in the total.

But I have no clue how to solve this on.

Who can help me.?

Greets RoyB

[v-xiaotang · Accepted solution]

Hi @RoyB1973 

I create a sample, the sample not having your data, but the logic is relatively simple, 

In this sample, create a measure to calculate the sum, then judge whether it is greater than 0.

sum =
MIN ( 'Table'[Aantal1] ) + MIN ( 'Table'[Aantal2] )

Points =
VAR _a =
    IF ( [sum] < 0, 0, [sum] )
VAR _b =
    SUMX ( ALL ( 'Table' ), ABS ( [sum] ) )
RETURN
    IF ( ISINSCOPE ( 'Table'[Aantal1] ), _a, _b )

Then sum its absolute values.

The principe of the dax code is:

   for the 1st row: -2-1= -3 <0, then Points = 0, abs value = abs(-3) = 3,

   for the 2nd row:-1+2= 1 >0, then Points = 1, abs value = abs(1) = 1,

......

So the Total row = 3+1+2+1+1=8

So you can try to put the part bellow into a measure, then judge the measure whether it is greater than 0.

If you need more help, please let me know. And please provide the sample file or some sample data, so that I can recreate the same pbix file.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[v-xiaotang · Accepted solution]

Hi @RoyB1973 

I create a sample, the sample not having your data, but the logic is relatively simple, 

In this sample, create a measure to calculate the sum, then judge whether it is greater than 0.

sum =
MIN ( 'Table'[Aantal1] ) + MIN ( 'Table'[Aantal2] )

Points =
VAR _a =
    IF ( [sum] < 0, 0, [sum] )
VAR _b =
    SUMX ( ALL ( 'Table' ), ABS ( [sum] ) )
RETURN
    IF ( ISINSCOPE ( 'Table'[Aantal1] ), _a, _b )

Then sum its absolute values.

The principe of the dax code is:

   for the 1st row: -2-1= -3 <0, then Points = 0, abs value = abs(-3) = 3,

   for the 2nd row:-1+2= 1 >0, then Points = 1, abs value = abs(1) = 1,

......

So the Total row = 3+1+2+1+1=8

So you can try to put the part bellow into a measure, then judge the measure whether it is greater than 0.

If you need more help, please let me know. And please provide the sample file or some sample data, so that I can recreate the same pbix file.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[RoyB1973 · Accepted solution]

Thanks, I managed it using your solution. Many Thanks

[amitchandak]

@RoyB1973 , If you need negative in total you need to use isinscope  on one or more column 

points measure  =

var _1 = Sumx(Table, [Qty]*10)

var _2 = Sumx(Table, if([Qty]<0, 0, [Qty]*10) 

return

if(isinscope(Table[Customer]) , _2, _1)

[RoyB1973]

@amitchandakthnx for the quick reply. 

The total of the negative = - 1030. But I don't need that one.
I only want the total in de Points Column to be 5350 and not 5350 - 1030 = 4320.
In the points column you see that when the growth < 0 there are no points. But in the total count these negative numbers are counted.