Forum Discussion

bhattshubham's avatar
bhattshubham
Frequent Visitor
6 years ago
Solved

Weekly and Monthly Aggreagations

Hi all,

 

I have the data of the following type for a large period of time.

 

PersonCallsConvertedDate
A10817-03-2020
B20617-03-2020
C302017-03-2020
A403124-03-2020
B302324-03-2020
C20724-03-2020
A10927-03-2020
B301127-03-2020
C20327-03-2020
A201301-04-2020
B30401-04-2020
C402801-04-2020
A10803-04-2020
B20603-04-2020
C302003-04-2020
A403107-04-2020
B302307-04-2020
C20707-04-2020
A10914-04-2020
B301114-04-2020
C20314-04-2020
A201317-04-2020
B30417-04-2020
C402817-04-2020

 

The conversion rate for the whole team is calculated at a week level and is calculated as,

Metric 1: Conversion Rate for each person for the week = SUM(Converted by that person)/SUM(Calls by that person)

Metric 2: Conversion Rate of the whole team for the week = AVERAGE(Conversion Rate for each person for the week)

 

When I select multiple weeks in the slicer, the number should be displayed as,

Metric 1: Conversion Rate for each person for selected weeks = AVERAGE(Metric 1 for that person for all weeks)

Metric 2: Conversion Rate of the whole team for selected weeks  = AVERAGE(Conversion Rate for each person for selected weeks)

 

I'm unable to think of easy ways to calculate this measure. Any help is hugely appreciated.

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Assuming I understood your requirements correctly. Let me know if this worked for you.

     

    Thanks

     

    MEASURES

    Total Calls = SUM(Conversions[Calls])
    Total Conversions = SUM(Conversions[Converted])
    Conversion Rate Person = DIVIDE([Total Conversions], [Total Calls])
    Conversion Rate Team = DIVIDE(
    SUMX(ALLSELECTED(Conversions), Conversions[Converted]) ,
    SUMX(ALLSELECTED( Conversions), Conversions[Calls]))


    DATE TABLE
    Date =
    VAR Dates =
    CALENDAR (
    DATE ( YEAR ( MIN ( 'Conversions'[Date] ) ), 1, 1 ),
    DATE ( YEAR ( MAX ( 'Conversions'[Date] ) ), 12, 31 )
    )
    RETURN
    ADDCOLUMNS (
    Dates,
    "Year", YEAR ( [Date] ),
    "Quarter", FORMAT ( [Date], "q" ),
    "Month", MONTH ( [Date] ),
    "Month_Name", FORMAT ( [Date], "mmmm" ),
    "Month_Short_Name", FORMAT ( [Date], "mmm" ),
    "Year_Quarter", YEAR ( [Date] ) & "-Q"
    & FORMAT ( [Date], "q" ),
    "Year_Month", YEAR ( [Date] ) & "-"
    & FORMAT ( [Date], "mm" )
    )

     

    WEEKENDING COLUMN

    WeekEnding = [Date] + 7 - WEEKDAY([DATE],2)

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Assuming I understood your requirements correctly. Let me know if this worked for you.

     

    Thanks

     

    MEASURES

    Total Calls = SUM(Conversions[Calls])
    Total Conversions = SUM(Conversions[Converted])
    Conversion Rate Person = DIVIDE([Total Conversions], [Total Calls])
    Conversion Rate Team = DIVIDE(
    SUMX(ALLSELECTED(Conversions), Conversions[Converted]) ,
    SUMX(ALLSELECTED( Conversions), Conversions[Calls]))


    DATE TABLE
    Date =
    VAR Dates =
    CALENDAR (
    DATE ( YEAR ( MIN ( 'Conversions'[Date] ) ), 1, 1 ),
    DATE ( YEAR ( MAX ( 'Conversions'[Date] ) ), 12, 31 )
    )
    RETURN
    ADDCOLUMNS (
    Dates,
    "Year", YEAR ( [Date] ),
    "Quarter", FORMAT ( [Date], "q" ),
    "Month", MONTH ( [Date] ),
    "Month_Name", FORMAT ( [Date], "mmmm" ),
    "Month_Short_Name", FORMAT ( [Date], "mmm" ),
    "Year_Quarter", YEAR ( [Date] ) & "-Q"
    & FORMAT ( [Date], "q" ),
    "Year_Month", YEAR ( [Date] ) & "-"
    & FORMAT ( [Date], "mm" )
    )

     

    WEEKENDING COLUMN

    WeekEnding = [Date] + 7 - WEEKDAY([DATE],2)