Renove record with Sum negative

[Jaweed]

 I would like to extract records from table, with 1 line per company.  Table is as per below, Negative sum of Outd Amount not to appear,

Customer           Invoice Num       Outd Amount        0-30d       31-60 d      61-90d       Over 90d        

A                              001                           100                                                   1000

A                             002                             300                              300

A                             003                           -500                                                                           -500

B                            004                             100                                100

B                            005                             400                                                    400

C                            006                              250                                                   250

Output: ( A does not appear as its sum of Outd Amount = -100)

B                                                                    500                               100            400

C                                                                    250                                                    250

Can anyone help. Struggling with this. Thank you.

[Jaweed]

Many thanks. 

[v-lili6-msft · Accepted solution]

hi, @Jaweed 

You could try these two ways as below:

1. Use this formula to create a new table

New Table = 
FILTER (
    SUMMARIZE (
        Basic,
        Basic[Customer],
        "Outd Amount", SUM ( Basic[Outd Amount] ),
        "0-30d", SUM ( Basic[0-30d] ),
        "31-60d", SUM ( Basic[31-60d] ),
        "61-90d", SUM ( Basic[61-90d] ),
        "Over 90d", SUM ( Basic[Over 90d] )
    ),
    [Outd Amount] > 0
)

Result:

2. create measure for each sum value column

Outd Amount measure = IF(CALCULATE(SUM(Basic[Outd Amount]))>0,CALCULATE(SUM(Basic[Outd Amount])))

0-30d measure = IF(CALCULATE(SUM(Basic[Outd Amount]))>0,CALCULATE(SUM(Basic[0-30d])))

31-60d measure = IF(CALCULATE(SUM(Basic[Outd Amount]))>0,CALCULATE(SUM(Basic[31-60d])))

61-90d measure = IF(CALCULATE(SUM(Basic[Outd Amount]))>0,CALCULATE(SUM(Basic[61-90d])))

Over 90d measure = IF(CALCULATE(SUM(Basic[Outd Amount]))>0,CALCULATE(SUM(Basic[Over 90d])))

Then drag Customer column and these measure into a table visual

Here is pbix file, please try it.

Best Regards,

Lin

[Jaweed]

Thanks a lot for the help