R3M Total Sum

[hoppahage]

Hello,

I'm trying to calculate the R3M Percentage of Total but somehow is not working. I was able to calculate the monthly percentage of total for the below example:

Period	Product	Monthly Sales	Total Sales Per Period	Market Share
201801	Apples	10	15	67%
201801	Bananas	4	15	27%
201801	Pears	1	15	7%
201802	Apples	9	16	56%
201802	Bananas	2	16	13%
201802	Pears	5	16	31%
201803	Apples	5	17	29%
201803	Bananas	5	17	29%
201803	Pears	7	17	41%
201804	Apples	3	21	14%
201804	Bananas	9	21	43%
201804	Pears	9	21	43%

Now, what I want to achieve is the same but for R3M, so far I was able to calculate the R3M Sales with the following measure:

Sales R3M = Calculate ( sum ( 'fact table'[sales]), datesinperiod ('dim calendar'[date], lastdate('dim calendar'[date]), -3 , month)) but I'm not sure how to calculate Total R3M Sales per Period and R3M Market Share. So for an example, please see below. I don't know how to calculate the last two columns of the below table:

Period	Product	Monthly Sales	Sales R3M	Total R3M Sales Per Period	R3M Market Share
201801	Apples	10	10	= 10 + 4 + 1	=10/ Total R3M Sales per Period
201801	Bananas	4	4	15	27%
201801	Pears	1	1	15	7%
201802	Apples	9	19	31	61%
201802	Bananas	2	6	31	19%
201802	Pears	5	6	31	19%
201803	Apples	5	24	48	50%
201803	Bananas	5	11	48	23%
201803	Pears	7	13	48	27%
201804	Apples	3	17	54	31%
201804	Bananas	9	16	54	30%
201804	Pears	9	21	54	39%

I've tried using SUM(Sales R3M), but it doesn't work because [Sales R3M] is a measure and not a column.

Any help will be highly appreciated and please let me know if you need further details.

Thank you so much!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[hoppahage]

Omg, thank you so much Ashish, it worked brilliantly!! 

I had some products filter out, so I change the ALL for ALLSelected and it worked! 😄 

Again, thank you very much!!

[Ashish_Mathur]

You are welcome.

[lbendlin]

ok, I'll bite - what is R3M ?

[hoppahage]

R3M being rolling three months, so the sum of the sales of the two previous months plus the current