Calculate a series based on weights of a given period for a time series

[EvertonRR]

Hi guys,

I was wondering if it is possible to create a visual like the one below:

If I select the date 2020-02-07 and the models A, B and C the weights for these Models and Versions on this date would be: 

Model	Version	Count	weights
A	AA	3	21.4%
A	B	2	14.3%
A	C	1	7.1%
B	AA	2	14.3%
B	B	1	7.1%
B	C	1	7.1%
C	AA	1	7.1%
C	B	2	14.3%
C	C	1	7.1%
	Total	14	100%

I would like to fix these weights, and calculate the weights * AVERAGE(PRICE) for all available dates, i.e. 2020-02-05, 2020-02-06 and 2020-02-07. The plot in the lower right would show the average price of all model+version weighted by the weights calculated on 2020-02-07, or any other date that I chosse on "Select a ref date" filter.

You can find the .pbix file in the following link:

https://drive.google.com/file/d/12Fayu0DLzljGeNNUn9mg9BiqxnCKmk6Y/view?usp=sharing

Let me know how to proceed on this problem please.

[v-kelly-msft]

Hi @EvertonRR，

You need 2 measures as below:

weight = COUNT(Sheet1[Version])/CALCULATE(COUNT(Sheet1[Version]),ALLEXCEPT(Sheet1,Sheet1[Date]))

Measure 2 = 'Sheet1'[weight]*AVERAGE(Sheet1[Price])

And you will see：

For the related .pbix file,pls click here.

Best Regards,
Kelly

[EvertonRR]

Actually, it is not what I am looking for. See image below:

The weights for 2020-02-07 sums 100%. I would like to have this same distribution for the other days, so I could multiple these fixed weights by the price average. So for day 2020-02-06, Model A, version AA, it should also be 21.43%, not 7.14%.

[v-kelly-msft]

Hi @EvertonRR ，

But in other days, the same version with a model has different counts,so do you want to calculate without considering their actual counts?

 
Best Regards,
Kelly

[EvertonRR]

It was the same file, I just added the count column, but I figured out how to solve this. In advanced editor, I grouped by Date and ID, where ID was Model + Version, and then I was able to calculate the weights and keep them fixed for other days. See image below and file attached with solution. Thank you for your help.