Calculate sales percentage based on Region for each month

[andymanu]

Hi All,

I need to calculate the Sales percentage for individual "City" based on the Total Sales value for the "Region".

Note

Region will have multiple Cities .

Basically, there are altogether three dimension rows ( named, "YearMonth", "Region" and "City") in the Pivot table and currently I have calculated the sales percentage of individual city by the respective Region. (Please refer the attached 'Sales Percentage QS').

Expression used to calculate the sales percentage is, "=Num(Sum(Sales) / Sum(TOTAL <YearMonth> Sales), '#,##0.00%')".

However, my requirement is to calculate the individual city sales percentage by "YearMonth" dimension.

Please refer the 'Expected Outcome Calculation on Excel' sheet for the detail information.

Appreciate your valuable feedback in advance.

Thanks.

Kind regards,

Andy

[andymanu]

Hi All,
I identified the issue with the above post in regards to my app.
Actually, the expression "=Num(Sum(Sales) / Sum(TOTAL <YearMonth> Sales), '#,##0.00%')" should be working fine but since I am using the Rank() function to filter the top three Regions out of the total, that is where it is creating the issue.
The said Rank function is, "=Aggr(If(Rank(sum(Sales),2)<=3, Region),YearMonth, Region)"
Thus, I believe that if I could combine the Rank function with the expression I stated first, I should be able to get the desired result.
But, I am not sure how exactly I could merge the above two statements to get the Total sales value for denominator for the respective month.
Could an expert help me to figure this out? Sorry, it was my mistake not mentioning that I was using the Rank function to filter dimension values cost I thought that it will not affect.
Thank you in advance.
Kind regards,
Andy