Calculate % based on combination of count and stored values

[rax99]

I have this basic table:

DayNo.	Customer	AgentsInvolved	CallID
0	AAA	1	1858
0	AAA	3	1859
2	AAA	1	1860
0	BBB	2	1862
0	CCC	1	1863
0	DDD	3	1864
9	DDD	1	1865
9	DDD	4	1866

I need to be able to find the % of customers who only contacted only once, and spoke to 1 agent only. So from the above example, out of 4 distinct customers only customer CCC falls into this category (1 call, 1 AgentInvolved)

So the Desired result would be: 1/4 or 25%

How can I create a measure to do this calc?

[harslan66]

Try this formula

% = DIVIDE(SUM(Table1[AgentsInvolved]),CALCULATE(DISTINCTCOUNT(Table1[Customer]),ALL(Table1[Customer])))

[rax99]

Thanks for your reply. However, this wouldnt work as we need to find where AgentsInvolved = 1 and Count(callid) = 1

[v-piga-msft · Accepted solution]

Hi @rax99,

Based your logic, you could try the measures below.

CallId_1 =
CALCULATE (
    DISTINCTCOUNT ( Table2[CallID] ),
    ALLEXCEPT ( Table2, 'Table2'[Customer] )
)
Count =
CALCULATE (
    SUM ( 'Table2'[AgentsInvolved] ),
    FILTER ( 'Table2', 'Table2'[AgentsInvolved] = 1 && [CallId_1] = 1 )
)
% = DIVIDE([Count],CALCULATE(DISTINCTCOUNT(Table2[Customer]),ALL(Table2)))

Here is the output.

Best  Regards,

Cherry