Calculate the average on the instances per month based on a given Status/Category

[jgulliver]

HI,

Needing some help again please. I have a Sales Leads report that I'm using as one of the "tables" in my PowerBI dashboard that has the following fields (there are other additional columns but they are not relevant for this purpose:

1. Created Date

2. Lead Status (New, Completed)

3. Interest Level (Hot, Warm, Cold)

I can do a count of the number of instances a certain contract status has come up per month but is it possible to calculate the average of each lead status per the month they were created? Alternatively, if it is possible I am assuming that the solution can apply to the Interest Level as well.

Thanks,

Joan

[Jihwan_Kim]

Hi,

I am not sure how your data model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your data model.

Contract count = 
COUNTROWS(DISTINCT(Data[Contract ID])) + 0

Average contract count: = 
AVERAGEX( DISTINCT('Calendar'[Month-Year]), [Contract count] )

[jgulliver]

Hi everyone,

Unfortunately I am still stuck on this problem. To add a bit more context, my data looks something

like this: 

Lead ID	Created Date	Status	Lead Type
123456	4 Sept 2022	Completed	Hot
789123	21 Sept 2022	In Progress	Warm
456789	20 Oct 2022	Completed	Hot

What I am trying to achieve is, as per what I have been requested to provide, display the average number of leads over the period of a project lifecycle. So for example, the commercial property development project spans across 6 months, the number of leads I get per month are as follows:

Month 1 - 5

Month 2 - 8

Month 3 - 10

Month 4 - 4

Month 5 - 14

Month 6 - 12

I should expect to get an average of 8.83. And to anticipate the next request in relation to this, at a later stage I may also be asked to provide an average number of leads over the course of the project per Lead Type so I am putting this out there as well.

Thanks for your help in advance.

[jgulliver]

Thanks for looking into this Jihwan.

All the data is stored in one table, which is derived from what is produced from 1 report. What it gives me is a list of leads generated along with the dates they were created and their status. I've been requested to show an average monthly number of leads but can't provide the average since the column (Lead Status) that I am referring to is a column that can't produce an average. Does that make sense?