Forum Discussion

hoppahage's avatar
hoppahage
Regular Visitor
3 years ago
Solved

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!

 

 

5 Replies

    • hoppahage's avatar
      hoppahage
      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!!

    • hoppahage's avatar
      hoppahage
      Regular Visitor

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