sum a column only if a value exists in another column

[Anonymous]

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?

[mahoneypat]

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

[Anonymous]

@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

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%

[mahoneypat · Accepted solution]

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

[Anonymous]

@mahoneypat  This worked great.  Thank you.  

[lbendlin]

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)

[Anonymous]

@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)?

[lbendlin]

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

[Anonymous]

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

[lbendlin]

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)