Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jgulliver
Frequent Visitor

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

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

 

3 REPLIES 3
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_0-1673838229566.png

 

Jihwan_Kim_1-1673838481974.png

 

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

 

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

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


Hi everyone,

 

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

like this: 

 

 

Lead IDCreated DateStatusLead Type
1234564 Sept 2022CompletedHot
78912321 Sept 2022In ProgressWarm
45678920 Oct 2022CompletedHot

 

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.

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?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.