cancel
Showing results for
Did you mean:
Highlighted
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

Accepted Solutions
Highlighted
Solution Specialist

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

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

Highlighted
Solution Sage

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

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 )``````

Thank you,
Antriksh Sharma
7 REPLIES 7
Highlighted
Community Champion

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

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)``````

Highlighted
Solution Specialist

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

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

Highlighted
Helper I

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

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?

Highlighted
Solution Sage

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

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

Thank you,
Antriksh Sharma
Highlighted
Helper I

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

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

Highlighted
Solution Sage

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

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 )``````

Thank you,
Antriksh Sharma
Highlighted
Community Champion

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

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)

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors