Create cumulative weighted average measure with changing weight

[avcr29]

Hello!

I really need help, i don't know how can i solve this, i've got a measure X and i need to get the cumulative weighted average of it. There are categories and per each category there's a determinated weight, now, so far i get the result but now there is one category that it will be considered only in some months (the data is trimestrals march, june, september and december, this new category will be considered on june and december) but because of that, the weights change in those months (june and december) so here is what i have:

Weigths:

Category	March	June	September	December
CB		0.2		0.2
CC	0.2	0.1	0.2	0.1
CM	0.4	0.35	0.4	0.35
CV	0.4	0.35	0.4	0.35

this is what i have:

Month	Category	measure x	weigth
June	CB	33.33%	0.2
March	CC	100.00%	0.2
June	CC	0%	0.1
March	CM	20.00%	0.4
June	CM	20.00%	0.35
March	CV	100.00%	0.4
June	CV	33.33%	0.35

but what i really want is this:

Month	Category	cumulative measure x	weigth	cumulative weighted average
June	CB	33.33%	0.2	6.67%
March	CC	100.00%	0.2	20.00%
June	CC	60.00%	0.1	6.00%
March	CM	20.00%	0.4	8.00%
June	CM	20.00%	0.35	7.00%
March	CV	100.00%	0.4	40.00%
June	CV	66.67%	0.35	23.33%

so if i want to get a total i'll get these results:

Month	TOTAL
March	68.00%
June	43.00%

the formulas i worked were these:

to get the weighted measure x:

weighted measure x=
VAR __CATEGORY_VALUES = VALUES('table'[Category])
RETURN
DIVIDE(
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE([measure x] * LASTNONBLANK('table'[weigth],1))
),
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE(LASTNONBLANK('table'[weigth],1))
)
)

to get the cumulative weigthed measure x:

cumulative weigthed measure x =
IF(
ISFILTERED('table'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = ENDOFMONTH('table'[Date].[Date])
VAR __DATE_PERIOD =
FILTER(DATESBETWEEN(
table'[Date].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, -COUNT('table'[Date].[Date]), MONTH)),
ENDOFMONTH('table'[Date].[Date])
),YEAR(__LAST_DATE)=YEAR('table'[Date].[Date]))
var resultado= CALCULATE(table[weighted measure x],
CALCULATETABLE(
SUMMARIZE(
VALUES('table'),
'table'[Date].[Year],
'table'[Date].[QuarterNo],
'table'[Date].[Quarter],
'table'[Date].[MonthNo],
'table'[Date].[Month]
),
__DATE_PERIOD
)

)
RETURN
if(ISBLANK(table[weighted measure x]),0,resultado)
)

these formulas worked well because the weights were the same each month but now they don't so i don't know exacly how can i modify the formulas or if there's another way to get that result. 

I would appreciate the help,

Thanks!

[v-piga-msft]

Hi @avcr29 ,

I have a little confused about your scenario.

If I understand your scenario correctly that you weighted table is your sample data and your desired output is the third table in your post?

If it is, could you share your formula of the measure x so that I could understand your logic better.

weighted measure x=
VAR __CATEGORY_VALUES = VALUES('table'[Category])
RETURN
DIVIDE(
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE([measure x] * LASTNONBLANK('table'[weigth],1))
),
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE(LASTNONBLANK('table'[weigth],1))
)
)

In addition, I cannot understand the logic why cumulative measure x for June of CC is 60%?

Best Regards,

Cherry

[avcr29]

Hi @v-piga-msft !

Thanks for your reply! 

Actually, my sample data is this:

Date	Month	Category	Type	Weight
01/03/2019	March	CM	Detractor	0.4
01/06/2019	June	CM	Promoter	0.35
04/06/2019	June	CB	Detractor	0.2
01/03/2019	March	CV	Promoter	0.4
04/06/2019	June	CV	Detractor	0.35
01/03/2019	March	CC	Promoter	0.2
04/06/2019	June	CC	Detractor	0.1
01/03/2019	March	CM	Promoter	0.4
04/06/2019	June	CM	Detractor	0.35
02/03/2019	March	CM	Promoter	0.4
02/06/2019	June	CM	Detractor	0.35
06/06/2019	June	CB	Promoter	0.2
09/03/2019	March	CV	Promoter	0.4
05/06/2019	June	CV	Promoter	0.35
03/03/2019	March	CC	Promoter	0.2
09/06/2019	June	CC	Promoter	0.1
09/03/2019	March	CM	Detractor	0.4
05/06/2019	June	CM	Promoter	0.35
06/03/2019	March	CM	Promoter	0.4
07/06/2019	June	CM	Promoter	0.35
03/06/2019	June	CB	Promoter	0.2
09/03/2019	March	CV	Promoter	0.4
21/06/2019	June	CV	Promoter	0.35
22/03/2019	March	CC	Promoter	0.2

the formula of measure x is:

measure x=
CALCULATE((COUNTAX(FILTER ( table, table[Type] = "Promoter" ), table[Type] )/COUNTA(table[type]))-(COUNTAX(FILTER ( table, table[Type] = "Detractor" ), table[Type]) /COUNTA(table[Type]))) 

the weighted table is just for explanatory purposes (how the weight change by month)  but it's not my sample data it's just one of the columns.

In addition, about your last question, june is that percent because that's the result if you apply the measure x since march till june.

category CC:

march  100.00%

june  60.00%

that's why in the third table (in my first post) when in the column "cumulative measure x"   says that value, is because i want that the cumulated results by month (march, (march+june), (march+june+september), (march+june+september+december)) are the ones i want to be ponderated with the weights.

i hope i have resoult your questions, thanks for the help.