cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bamba98
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
stevedep
Super User I
Super User I

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

View solution in original post

7 REPLIES 7
harshnathani
Super User III
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.

 

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!

 

 

stevedep
Super User I
Super User 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? 

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

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

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

View solution in original post

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

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.