% grand total of each category in each month using multiple 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:

[measure] % 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 do this? Thank you so much!

[v-frfei-msft]

Hi @trdoan,

I made one sample for your reference. Please check the following steps as below.

1. Create a calculated table.

Table = CALCULATETABLE('Full Data','Full Data'[Product Catergory]in VALUES('Sample'[Catergory]))

2. Create the measures as below.

average = SUMX('Table',AVERAGE('Table'[Quantity]))

all = SUMX(ALL('Table'[Product Catergory]),[average])

% Grand Total = [average]/[all]

Please chekc the pbix as attached.

Regards,

Frank

[trdoan]

Hi @v-frfei-msft, thank you for your response, however, your answer doesn't quite work out for my data. I did the exact same thing as you did and the "average", "all" & "% Grand Total" share the same values.

Here is what I got:

Do you know why?

Thanks a lot!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.