cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

sum a column only if a value exists in another column

Hi,

 

I am looking to sum a column only if a value exists in another column, where the tables are not summarized data, but detailed and only summarized in the visuals and measures (this is important as to retain the ability for the user to adjust the dimension filters).

 

For example, I have an account table.

Account_ID

Group

1

A

2

A

3

A

4

B

5

B

6

C

7

C

8

C

9

C

 

I also have a user table

User

Account_id

111

1

222

1

333

1

444

2

555

2

666

3

777

3

888

4

 

 

I also have a usage table, that tells me if a user (and therefore an account) has usage.

user

date logged in

111

1/1/2020

111

1/2/2020

222

1/1/2020

222

1/2/2020

222

1/1/2020

333

1/2/2020

333

1/1/2020

444

1/2/2020

555

1/2/2020

666

1/2/2020

777

1/2/2020

888

1/2/2020

 

 

(note, I need all of the tables because the account and user table contain other dimensions about the accounts and users that are needed)

 

I have created the following summary table

 

Group

Num of accounts

Accounts logged in

% logged in

A

3

3

100%

B

2

1

50%

C

4

 

 

Total

9

4

44%

 

Where

  • num of accounts = DISTINCTCOUNT(account[account_id])
  • accounts logged in = CALCULATE(DISTINCTCOUNT(account[account_id]),filter(usage,count(usage[user])))

 

This works for the table above, but the total of 44% if not what I want to show.  If there is not value in accounts logged in for a group, then I don’t want to count the value in num of accounts in the denominator (i.e. instead of 4/9 = 40%, I want 4/5 = 80%).  The resulting table should look like this.

 

Group

Num of accts 2

% logged in 2

% logged in

A

3

3

100%

B

2

1

50%

C

 4

 

 

Total

5

4

80%

 

How can I write the % to ignore the ‘4’ accounts for group C because they haven’t logged in?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: sum a column only if a value exists in another column

Here is a new expression for your num of accounts measure that gets the desired results I believe.

 

num of accounts 2 =
VAR __groupswithlogin =
    CALCULATETABLE ( VALUES ( Account[Group] ), usage )
RETURN
    SUMX ( __groupswithlogin, CALCULATE ( DISTINCTCOUNT ( Account[Account_ID] ) ) )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 





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

Proud to be a Super User!




View solution in original post

9 REPLIES 9
Highlighted
Super User V
Super User V

Re: sum a column only if a value exists in another column

You can combine two measures, one for the individual items in the visual, and one for the totals

 

Result = if(hasonevalue(Group),detail measure, totals measure)

Highlighted
Helper I
Helper I

Re: sum a column only if a value exists in another column

@lbendlin  thank you.  I follow the hasonevalue to get the detailed row vs total row to have different calculations, but what would you do to calculate the total row denominator (i.e. sum A and B but not C)?

Highlighted
Super User VI
Super User VI

Re: sum a column only if a value exists in another column

Please try these expressions instead

 

  • num of accounts = IF(HASONEVALUE(account[Group]), DISTINCTCOUNT(account[account_id), CALCULATE(DISTINCTCOUNT(account[account_id]), Usage)
  • accounts logged in = CALCULATE(DISTINCTCOUNT(account[account_id]), Usage)
  • % logged in = DIVIDE([accounts logged in], [num of accounts])

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





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

Proud to be a Super User!




Highlighted
Super User V
Super User V

Re: sum a column only if a value exists in another column

tell me the rationale for excluding C from the percentage calculation. To me that feels like misrepresenting the facts.

Highlighted
Helper I
Helper I

Re: sum a column only if a value exists in another column

@mahoneypat 

 

thank you.  I tired num of accounts = IF(HASONEVALUE(account[Group]), DISTINCTCOUNT(account[account_id), CALCULATE(DISTINCTCOUNT(account[account_id]), Usage).

 

the end result was below.  note the first column is just to show the total accts in each group for context.  For num of accts (your formula), while the detail level is correct, the total row is pulling the total accounts with usage (prev column), not the total accounts for the group.  it should pull the sum total of accounts for the group if any 1 account in that group has usage

 

Groupnum of accts (total)num of accts logged in (your formula)num of accts (your formula)% logged in
A333100%
B21250%
C4 4 
Total944100%
Highlighted
Helper I
Helper I

Re: sum a column only if a value exists in another column

@lbendlin   what i am showing is a small part of a larger report.  The idea is that there are groups (50-100).  Each group has a set number of accounts that is going through a process (5-10 stages in the process).  we want to track each groups success in each phase and the overall success.  Each group is at a different phase of the process.  so in the example i provided a blank cell doesn't represent no usage, ino usages means that the group has not yet started that phase of the process, therefore we don't want to calculate those accounts (i.e. the denominator) in the total success rate.  if we did, the phase % success would be artifically low because of the groups/accounts we are counting.

Highlighted
Super User VI
Super User VI

Re: sum a column only if a value exists in another column

Here is a new expression for your num of accounts measure that gets the desired results I believe.

 

num of accounts 2 =
VAR __groupswithlogin =
    CALCULATETABLE ( VALUES ( Account[Group] ), usage )
RETURN
    SUMX ( __groupswithlogin, CALCULATE ( DISTINCTCOUNT ( Account[Account_ID] ) ) )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 





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

Proud to be a Super User!




View solution in original post

Highlighted
Super User V
Super User V

Re: sum a column only if a value exists in another column

An easier solution would be a visual filter to exclude groups like C. As you may have noticed it is rather difficult to achieve what you want with a DAX measure. (A calculated column might work)

Highlighted
Helper I
Helper I

Re: sum a column only if a value exists in another column

@mahoneypat  This worked great.  Thank you.  

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

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.