Forum Discussion

harib's avatar
harib
Post Patron
7 years ago
Solved

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 .

 

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

 

Thanks in advance

 

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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

4 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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