cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
johnmcdonnell Frequent Visitor
Frequent Visitor

Frequency of CRM Activities

Hi There!

 

I am creating a report in Power BI to analyse some CRM KPIs - one of which is not working for me.  My data model includes an Activities table and a Customers table.  I'd like to be able to count the number of activities for each customer but I would also like to be able to filter this using slicers such as Responsible (sales rep that visited the customer), Sales Team, Customer rating or by Month/Quarter (I also have a date table). The purpose is to be able to populate a bar chart showing the number of activities per customer on the x-axis and the number of customers on the y-axis.  This tells us, for example, 150 customers were visited zero times, 100 customers were visited once, 50 customers were visited twice and so on.

 

I have created the calculated column below in the Customers table which correctly counts the total number of activities for each customer but the slicers don't work with such calculated columns.  Can this be acheived another way?

 

MyActivities = countrows(relatedtable(Activities))

 

Any help would be very much appreciated!

 

Thanks!

John

2 REPLIES 2
v-ljerr-msft Super Contributor
Super Contributor

Re: Frequency of CRM Activities

Hi @johnmcdonnell,

 

Not like measures, calculate columns/tables are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report.

 

However, only column can be shown as X-Axis on a Chart visual. So I don't think there is a possible solution to do exactly what you want currently. 

 

In addition, an alternative solution could be that use the formulas below to create two measures, then show the measures, and the Customer column on a Table/Matrix. Though, duplicate values will be shown on the Table/Matrix, you will partly get the expected result. Smiley Happy

MyActivitiesMeasure = COUNTROWS ( Activities )
Count of Customer =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( Activities ),
            Activities[Customer],
            "Count of Activities", COUNTROWS ( Activities )
        ),
        [Count of Activities] = COUNTROWS ( Activities )
    )
)

 

Regards

johnmcdonnell Frequent Visitor
Frequent Visitor

Re: Frequency of CRM Activities

Thanks for the effort v-ljerr-msft, I'll try this out and let you know how I get on.  Much appreciated!