cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rax99 Member
Member

Calculate % based on combination of count and stored values

I have this basic table:

DayNo.CustomerAgentsInvolvedCallID
0AAA11858
0AAA31859
2AAA11860
0BBB21862
0CCC11863
0DDD31864
9DDD11865
9DDD41866

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculate % based on combination of count and stored values

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.

 

result.PNG

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Highlighted
harslan66 Frequent Visitor
Frequent Visitor

Re: Calculate % based on combination of count and stored values

Try this formula

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

 

rax99 Member
Member

Re: Calculate % based on combination of count and stored values

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

Community Support Team
Community Support Team

Re: Calculate % based on combination of count and stored values

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.

 

result.PNG

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.