Summing values in a column based on two conditions in other columns

[bamba98]

I have a huge table, and example of four columns from the table is as follows:

In this table i have c_id (clients), p_id (periods) and val=values. I want to be able to calculate tot_val. The way I want tot_val to be calculated is, for example, for c_id=1 and p_id=19931201, we have that val = (10,15,30), so tot_val should return 55 on all three rows with c_id=1 and p_id=19931201. I tried many options but did not get the correct results. Please let me know if you have any suggestions on how to do this.

[stevedep · Accepted solution]

Calculate (sum(Val), allexcept(Cid,oid))

[harshnathani]

HI @bamba98 ,

Create a measure

Total Value =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[C_Id]
            = MAX ( 'Table'[C_Id] )
            && 'Table'[P_Id]
                = MAX ( 'Table'[P_Id] )
            && 'Table'[Outstanding] > 0
    )
)

Do a Visual Filter for Outstanding.

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[stevedep · Accepted solution]

Calculate (sum(Val), allexcept(Cid,oid))

[bamba98]

Thank you very much @stevedep , that works fine!

However, I noticed that there is another column "Outstanding" in my table that contains values which are zero. How can I exclude these rows in my calculation? 

[AntrikshSharma]

Just include that column as well in the ALLEXCEPT ( Table, Column1, Column2 )

[bamba98]

@AntrikshSharma This return the values of val in tot_val. Allow me to explain it better:

Let zoom in to c_id=1 and p_id=19931201.

What I want is for the expression to return the sum of val where  outstanding is greater than 0. With the solution of @stevedep , I get tot_val = 110, but I want it to return 55 as it should ignore the rows where outstanding =0.

[AntrikshSharma · Accepted solution]

Something like this?

TotalValue =
VAR Client = Bamba[C_ID]
VAR Periods = Bamba[P_ID]
VAR Outstanding = Bamba[Outstanding]
VAR SameRows =
    FILTER (
        Bamba,
        Bamba[C_ID] = Client
            && Bamba[P_ID] = Periods
            && Bamba[Outstanding] <> 0
    )
VAR Result =
    CALCULATE ( SUM ( Bamba[Val] ), SameRows )
RETURN
    IF ( Outstanding = 0, 0, Result )

[lbendlin]

You can do this in many ways, with SUMX, ALLEXCEPT etc.  Here's a pedestrian method:

tot_val = 
var c= selectedvalue(Table[c_id])
var p= selectedvalue(Table[p_id])
return calculate(sum(Table[val]),allselected(Table),Table[c_id]=c,Table[p_id]=p)