cancel
Showing results for
Search instead for
Did you mean:
Regular Visitor

## Group average

Hi ,

When you select range in slicer, opportunity name count should come based on the lead date average . I need out put like below image.

Formulas which i have used:

Lead  Date average = 'Opportunity'[Close Date].[Date]-'Opportunity'[Last Modified].[Date]

Opportunity Name Count = COUNT('Opportunity'[Opportunity Name])

Slicer values should link to lead date average. when it have relationship filters will work and values will change .

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Group average

Hi @harib

You may create two calculated columns as below. Attached the sample file for your reference.

```Lead  Date average2 =
AVERAGEX (
FILTER ( Sheet1, Sheet1[From Stage] = EARLIER ( Sheet1[From Stage] ) ),
Sheet1[Close Date].[Date] - Sheet1[Last Modified].[Date]
)
```
```Avg_Days =
IF (
Sheet1[Lead  Date average2] >= 1
&& Sheet1[Lead  Date average2] <= 30,
"1-30",
IF (
Sheet1[Lead  Date average2] >= 31
&& Sheet1[Lead  Date average2] <= 60,
"31-60",
IF (
Sheet1[Lead  Date average2] >= 61
&& Sheet1[Lead  Date average2] <= 90,
"61-90",
IF (
Sheet1[Lead  Date average2] >= 91
&& Sheet1[Lead  Date average2] <= 120,
"91-120"
)
)
)
)
```

Regards,

Cherie

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

## Re: Group average

Hi @harib

You may create two calculated columns as below. Attached the sample file for your reference.

```Lead  Date average2 =
AVERAGEX (
FILTER ( Sheet1, Sheet1[From Stage] = EARLIER ( Sheet1[From Stage] ) ),
Sheet1[Close Date].[Date] - Sheet1[Last Modified].[Date]
)
```
```Avg_Days =
IF (
Sheet1[Lead  Date average2] >= 1
&& Sheet1[Lead  Date average2] <= 30,
"1-30",
IF (
Sheet1[Lead  Date average2] >= 31
&& Sheet1[Lead  Date average2] <= 60,
"31-60",
IF (
Sheet1[Lead  Date average2] >= 61
&& Sheet1[Lead  Date average2] <= 90,
"61-90",
IF (
Sheet1[Lead  Date average2] >= 91
&& Sheet1[Lead  Date average2] <= 120,
"91-120"
)
)
)
)
```

Regards,

Cherie

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

## Re: Group average

@v-cherch-msft

HI Cherie

Thanks a lot. It working what i expected. Keep rocking dude.

Thanks

Hari

Regular Visitor

## Re: Group average

@v-cherch-msft

Hi Bro

I have a stage column.i want the count of each stage and  i want ratio (Each stage count  devided by  total count) .

I need out put like below table

Thanks in advance

Community Support Team

## Re: Group average

Hi @harib

I would suggest you create a new thread on forum so that more community members can see it and provide advice. Please remember to post dummy data and desired result.

Regards,

Cherie

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