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

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

Proud to be a Super User!

9 REPLIES 9
Highlighted 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

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

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

• 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

Proud to be a Super User!

Highlighted 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

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

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

 Group num of accts (total) num of accts logged in (your formula) num of accts (your formula) % logged in A 3 3 3 100% B 2 1 2 50% C 4 4 Total 9 4 4 100%
Highlighted 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

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

Proud to be a Super User!

Highlighted 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

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

@mahoneypat  This worked great.  Thank you.

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

We're excited to announce our first cross-community 'Can You Solve These?' challenge! #### 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

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5. Top Solution Authors
Top Kudoed Authors
Users online (1,124)