topic Summing values in a column based on two conditions in other columns in DAX Commands and Tips

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

bamba98 — Fri, 03 Jul 2020 15:46:59 GMT

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.

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

lbendlin — Fri, 03 Jul 2020 16:09:56 GMT

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)

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

stevedep — Fri, 03 Jul 2020 17:49:57 GMT

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

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

bamba98 — Sun, 05 Jul 2020 11:54:41 GMT

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?

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

AntrikshSharma — Sun, 05 Jul 2020 13:51:36 GMT

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

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

bamba98 — Sun, 05 Jul 2020 14:31:06 GMT

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.

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

AntrikshSharma — Sun, 05 Jul 2020 14:53:21 GMT

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 )

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

Anonymous — Sun, 05 Jul 2020 16:08:04 GMT

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!