cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
harib Regular Visitor
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 .
New.JPG

 

Source : https://drive.google.com/open?id=1hlL6bhutQkrRefNlafCFRFz87UtAvpHZ

 

Thanks in advance

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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
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.
harib Regular Visitor
Regular Visitor

Re: Group average

@v-cherch-msft

 

HI Cherie

 

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

 

Thanks

Hari Smiley Happy

harib Regular Visitor
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

 

test.JPG

 

 

Source : https://drive.google.com/open?id=19zO_1z4cvdNlPv6hTNfUJG1v9sVhE-Sn

 

Thanks in advance

Community Support Team
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.