Sum rows base on another column values and remove rows if net to zero

[Anonymous]

Good Day All,

I'd like to remove the rows if Type = Balance, Revalue Flag = N  and its  Equal Base amount is net to zero and Base Amt is Blank

Thanks in advance.

Acct     Base Amt         Currency Bal          Equal Base           Amt Type    Revalue Flag
1000                                  -150                  -100                    Balance            N
1000                                   150                    100                    Balance            N
2000                                                           - 300                   Transaction      Y
2000                                                             300                   Transaction       Y                                  

3000                                 100                      110                    Balance            Y

3000                                 -100                    -110                    Balance           Y

[v-luwang-msft]

Hi @Anonymous ,

Use the following dax :

Amttype2 =
VAR baseamt =
    IF ( MAX ( 'Table'[Base Amt ] ) = BLANK (), 0, 1 )
VAR sumequal =
    CALCULATE (
        SUM ( 'Table'[Equal Base ] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Acct  ] = MAX ( 'Table'[Acct  ] ) )
    )
VAR equal =
    IF ( sumequal = 0, 0, 1 )
VAR amt =
    IF ( MAX ( 'Table'[Amt Type] ) = "Balance", 0, 1 )
VAR revalue =
    IF ( MAX ( 'Table'[Revalue Flag] ) = "N", 0, 1 )
VAR total = baseamt + equal + amt + revalue
VAR last =
    IF ( total = 0, BLANK (), MAX ( 'Table'[Amt Type] ) )
RETURN
    last

Then you will see:

Best Regard

Lucien Wang

[Anonymous]

Thanks, actually, i want to remove the 1st and second line. If  Sum of accounts Equal Base = 0, Base Amt=0 Amt type = Balance, Revalue flag = N

[v-luwang-msft]

Hi @Anonymous ,

You could try the following measure to create a new table ,and new table will remove what you want :

solution 1,

Table 2 = CALCULATETABLE('Table',FILTER(ALL('Table'),'Table'[Amt Type]<>"Balance"||'Table'[Base Amt ]<>BLANK()||'Table'[Revalue Flag]<>"N"||'Table'[Equal Base ]<>0))

Before:

New:

solution 2,

Create measure use following dax:

Measure=

var baseamt=IF(MAX('Table'[Base Amt ])=BLANK(),0,1)  

var equal=IF(max('Table'[Equal Base ])=0,0,1) 

var amt=if(MAX('Table'[Amt Type])="Balance",0,1)  

var revalue=IF(MAX('Table'[Revalue Flag])="N",0,1)

var total=baseamt+equal+amt+revalue  

return total

Then create table base on the measure:

Table 3 = CALCULATETABLE('Table',FILTER(ALL('Table'),'Table'[measure]<>0))

Hope it helpful for you!

Click  here  to download pbix if you need.

Best Regard

Lucien Wang

[amitchandak]

@Anonymous , Looking at data you will not get any row as all account sum is 0. what is the expected output?

A measure like this

sumx(filter(summarize(filter(Table, not (Table[Type] = "Balance" && table[Revalue Flag] = "N")), Table[Acc], "_1", Sum(Table[Equal Base])),[_1]<>0),[Equal Base])