Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
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!!
You are welcome.
ok, I'll bite - what is R3M ?
R3M being rolling three months, so the sum of the sales of the two previous months plus the current
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
81 | |
70 | |
49 |
User | Count |
---|---|
143 | |
124 | |
107 | |
60 | |
55 |