cancel
Showing results for
Did you mean:
Highlighted
Member

## % GT of each month for each category using different tables

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!

2 REPLIES 2
Community Support Team

## Re: % GT of each month for each category using different tables

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%:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

## Re: % GT of each month for each category using different tables

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!