Forum Discussion
R3M Total Sum
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!
5 Replies
- Ashish_MathurSuper User
- hoppahageRegular Visitor
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_MathurSuper User
You are welcome.
- lbendlinSuper User
ok, I'll bite - what is R3M ?
- hoppahageRegular Visitor
R3M being rolling three months, so the sum of the sales of the two previous months plus the current