cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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

Hi everyone,

 

Here is my data:

 

Sample table:

StoreCatergory
1A
2B
3C

 

Full Data table:

Store NameProduct CatergoryQuantityPosting Date
1A40July 7, 2018
1B400June 22, 2018
2C15July 9, 2018
5A15March 5, 2018
1E150February 20, 2018
4D45January 29, 2018
1E65August 31, 2018
2C95April 10, 2018
1C145April 3, 2018
4B145January 3, 2018
5B65January 3, 2018
1B145April 3, 2018
4A150January 3, 2018
5C155January 3, 2018
2D350January 3, 2018
1D155May 3, 2018
5A20January 3, 2018
4C25January 3, 2018
3B260January 3, 2018
3C65January 3, 2018
2C65January 3, 2018
3B60January 3, 2018
2A10May 29, 2018
3E15April 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 ABCTotal
Jan25%25%50%100%
Feb10%45%45%100%
Mar10%10%80%100%
Apr60%40%0%100%
May0%0%100%100%
Jun15%65%20%100%
Jul90%5%5%100%
Aug90%10%0%100%
Sep80%15%5%100%
Oct10%90%0%100%
Nov0%0%100%100%
Dec0%0%100%100%

 

Can anyone please show me how I can do this? Thank you so much!

3 REPLIES 3
Highlighted
Community Support
Community Support

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

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]

Capture.PNG

 

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 others find it more quickly.
Highlighted
Helper III
Helper III

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:

Capture.JPG

 

Do you know why?

 

Thanks a lot!

 

 

 

Highlighted
Super User IV
Super User IV

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

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors