Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Summing values in a column based on two conditions...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

bamba98

Helper I

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-03-2020
08:46 AM

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.

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

stevedep

Solution Specialist

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-03-2020
10:49 AM

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

Highlighted

Thank you,

Antriksh Sharma

AntrikshSharma

Solution Sage

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-05-2020
07:49 AM

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
##

lbendlin

Community Champion

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-03-2020
09:09 AM

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

stevedep

Solution Specialist

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-03-2020
10:49 AM

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

Highlighted
##

bamba98

Helper I

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-05-2020
04:48 AM

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
##

AntrikshSharma

Solution Sage

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-05-2020
06:51 AM

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

Thank you,

Antriksh Sharma

Thank you,

Antriksh Sharma

Highlighted
##

bamba98

Helper I

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-05-2020
07:29 AM

@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

Thank you,

Antriksh Sharma

AntrikshSharma

Solution Sage

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-05-2020
07:49 AM

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
##

harshnathani

Community Champion

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-05-2020
09:08 AM

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!**

Announcements

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

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

Top Solution Authors

User | Count |
---|---|

42 | |

35 | |

25 | |

15 | |

15 |

Top Kudoed Authors

User | Count |
---|---|

51 | |

51 | |

27 | |

22 | |

20 |