Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hoppahage
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:

 

PeriodProductMonthly SalesTotal Sales Per PeriodMarket Share
201801Apples101567%
201801Bananas41527%
201801Pears1157%
201802Apples91656%
201802Bananas21613%
201802Pears51631%
201803Apples51729%
201803Bananas51729%
201803Pears71741%
201804Apples32114%
201804Bananas92143%
201804Pears92143%

 

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:

 

PeriodProductMonthly SalesSales R3MTotal R3M Sales Per PeriodR3M Market Share
201801Apples1010= 10 + 4 + 1=10/ Total R3M Sales per Period
201801Bananas441527%
201801Pears11157%
201802Apples9193161%
201802Bananas263119%
201802Pears563119%
201803Apples5244850%
201803Bananas5114823%
201803Pears7134827%
201804Apples3175431%
201804Bananas9165430%
201804Pears9215439%

 

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
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.