cancel
Showing results for
Did you mean:
Regular Visitor

## 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!

1 ACCEPTED SOLUTION
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
5 REPLIES 5
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular 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!!

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

ok, I'll bite - what is R3M ?

Regular Visitor

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

Announcements

#### Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

#### Power BI May 2023 Update

Find out more about the May 2023 update.