cancel
Showing results for
Did you mean:  Helper I

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

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.

2 ACCEPTED SOLUTIONS  Super User I

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

Proud to be a Super User!  Resident Rockstar

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 )`````` 7 REPLIES 7  Super User III

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)  Super User I

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

Proud to be a Super User!  Helper I

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?  Resident Rockstar
Just include that column as well in the ALLEXCEPT ( Table, Column1, Column2 )  Helper I

@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.  Resident Rockstar

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 )``````   Super User III

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)`````` Announcements #### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories. #### Power BI Dev Camp - September 30th, 2021  