Forum Discussion

Jaweed's avatar
Jaweed
Icon for Helper III rankHelper III
7 years ago
Solved

Renove record with Sum negative

 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.

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

3 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Jaweed
      Icon for Helper III rankHelper III
      7 years ago

      Thanks a lot for the help