% GT of each month for each category using different tables

[trdoan]

Hi everyone,

Here is my data:

Sample table:

Store	Catergory
1	A
2	B
3	C

Full Data table:

Store Name	Product Catergory	Quantity	Posting Date
1	A	40	July 7, 2018
1	B	400	June 22, 2018
2	C	15	July 9, 2018
5	A	15	March 5, 2018
1	E	150	February 20, 2018
4	D	45	January 29, 2018
1	E	65	August 31, 2018
2	C	95	April 10, 2018
1	C	145	April 3, 2018
4	B	145	January 3, 2018
5	B	65	January 3, 2018
1	B	145	April 3, 2018
4	A	150	January 3, 2018
5	C	155	January 3, 2018
2	D	350	January 3, 2018
1	D	155	May 3, 2018
5	A	20	January 3, 2018
4	C	25	January 3, 2018
3	B	260	January 3, 2018
3	C	65	January 3, 2018
2	C	65	January 3, 2018
3	B	60	January 3, 2018
2	A	10	May 29, 2018
3	E	15	April 18, 2018

I know how to do percentage formula where all data used is from the same table, however, my Category column (containing A, B, C,..) is from Sample table whilst all numeric data used for calculation is from Full Data table (The 2 tables are in a 1-many relationship, linked by Category & Product Catergory columns).

I only want to know the % grand total of each category in each month from the Sample table, using data from the Full Data table which looks like this:

 % Grand Total = (Average Quantity of category A in month 1 / Average Quantity of all categories in month 1)*100

Month	A	B	C	Total
Jan	25%	25%	50%	100%
Feb	10%	45%	45%	100%
Mar	10%	10%	80%	100%
Apr	60%	40%	0%	100%
May	0%	0%	100%	100%
Jun	15%	65%	20%	100%
Jul	90%	5%	5%	100%
Aug	90%	10%	0%	100%
Sep	80%	15%	5%	100%
Oct	10%	90%	0%	100%
Nov	0%	0%	100%	100%
Dec	0%	0%	100%	100%

Can anyone please show me how I can create such measure? Thank you so much!

[v-danhe-msft]

Hi @trdoan,

Based on your data, I could not figure out how to calculate the number from your formula:

% Grand Total = (Average Quantity of category A in month 1 / Average Quantity of all categories in month 1)*100

For example, in May, your data table is like:

It seemed the category A is 100%(if your formula about all categories just includes 'A', 'B' and 'C').

But with your desired result, it seemed 'C' is 100%:

Could you please offer me more information about it?

Regards,

Daniel He

[trdoan]

Hi @v-danhe-msft,

The % table was merely a quick demonstration for what I'd like to achieve and it has no relation to the data whatsoever. Sorry for this confusion!