Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in BI by (17.6k points)

I have a basic table that looks like this:

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 Power BI measure to do this calc? 

1 Answer

0 votes
by (47.2k points)
  • Use this measure:

Desired Result = VAR summarizetable = SUMMARIZECOLUMNS ( 'table'[Customer], "Calls", COUNT ( 'table'[CallID] ), "Agents", SUM ( 'table'[AgentsInvolved] ), "Day", SUM ( 'table'[DayNo.] ) ) RETURN COUNTROWS ( FILTER ( summarizetable, [Calls] = 1 && [Agents] = 1 && [Day] = 0 ) ) / COUNTROWS ( summarizetable )

  • The summarized table created on the fly in VAR summarizetable looks like this:

https://i.stack.imgur.com/Db4nJ.png

31k questions

32.8k answers

501 comments

693 users

Browse Categories

...