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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.