Weekly and Monthly Aggreagations

[bhattshubham]

Hi all,

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

Person	Calls	Converted	Date
A	10	8	17-03-2020
B	20	6	17-03-2020
C	30	20	17-03-2020
A	40	31	24-03-2020
B	30	23	24-03-2020
C	20	7	24-03-2020
A	10	9	27-03-2020
B	30	11	27-03-2020
C	20	3	27-03-2020
A	20	13	01-04-2020
B	30	4	01-04-2020
C	40	28	01-04-2020
A	10	8	03-04-2020
B	20	6	03-04-2020
C	30	20	03-04-2020
A	40	31	07-04-2020
B	30	23	07-04-2020
C	20	7	07-04-2020
A	10	9	14-04-2020
B	30	11	14-04-2020
C	20	3	14-04-2020
A	20	13	17-04-2020
B	30	4	17-04-2020
C	40	28	17-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.

[amitchandak]

@bhattshubham , Please find this file. This file deal with this week vs last week

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

[glissando · Accepted solution]

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)