cancel
Showing results for
Did you mean:
Highlighted
Member

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

[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!

3 REPLIES 3
Community Support Team

Re: % grand total of each category in each month using multiple tables

Hi @trdoan,

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

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

Re: % grand total of each category in each month using multiple tables

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!

Super User

Hi,