cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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:

 

bamba98_0-1593790719419.png

 

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
Solution Specialist

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

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

View solution in original post

Highlighted
Super User II
Super User II

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 )

 

bamba.PNG


Thank you,
Antriksh Sharma

View solution in original post

7 REPLIES 7
Highlighted
Super User V
Super User V

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
Solution Specialist

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

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

View solution in original post

Highlighted
Helper I
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
Super User II
Super User II

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

bamba98_0-1593959137418.png

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
Super User II
Super User II

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 )

 

bamba.PNG


Thank you,
Antriksh Sharma

View solution in original post

Highlighted
Super User V
Super User V

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.

 

1.jpg

 

Regards,

Harsh Nathani


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

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

 

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

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

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.