cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MCBMarketing
New Member

Calculating percentage of a subcategory related to total sum of another table

Hi,

 

I am looking for help with an issue that I have been trying to solve, but cannot figure it out.

 

I want to calculate the market share, based on the actual volume divided by the market volume. I have two tables; one with the actual volume per sales organization within the same country and one with the market volume of the country. I would like to calculate the market share per sales organization. The data I have looks like this:

 

Table one with actual volume:

YearCountrySales OrganizationMaterialMaterial shapeActual volume
2019NetherlandsAWoodPlanks200
2020NetherlandsAWoodPlanks190
2019NetherlandsBWoodPlanks20
2020NetherlandsBWoodPlanks18

 

Table 2 with market volume:

YearCountryMaterialMaterial shapeMarket volume
2019NetherlandsWoodPlanks950
2020NetherlandsWoodPlanks800

 

I would like to be able to filter on Year, Country, Sales Organization, Material and Material shape.

 

The measure I have tried looks like this, but then it does not take the Sales Organization into account:

Market share = DIVIDE(
SUM ( 'Table1'[Actual volume] ),
SUM ( 'Table2'[Market volume] ),
0
)
 
Could someone help me out with this?
 
Thanks a lot!
1 ACCEPTED SOLUTION

@MCBMarketing  Hey 

For bridge table . I have written a conditional col. in power query.

HarishKM_0-1618486462446.png

 

select your key col then add that a query remove duplicate and convert it as table . now connect .

to avoid duplicate number for table .use merge query to get sales org. name in your 2nd table .

 

HarishKM_1-1618486734827.png

 

And post bringing sales org in table 2 

 

HarishKM_2-1618486949858.png

 

 

View solution in original post

6 REPLIES 6
Jihwan_Kim
Community Champion
Community Champion

Hi, @MCBMarketing 

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

 

All measures are in the sample pbix file.

 

 

Picture1.pngPicture2.png

 

 

https://www.dropbox.com/s/gvod7gup3wcih14/mcb.pbix?dl=0 

 

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

HarishKM
Responsive Resident
Responsive Resident

@MCBMarketing  Hey 

You can refer below image 

 

HarishKM_0-1618479503708.png

 

Schema Model 

 

HarishKM_1-1618479544893.png

 

 

Bridge table contain unique record of key . Key based on country key ( i have create a conditional col. based on country . if country = "India' then output = 1 Like that and with the help of that key define relationship.

 

You can slice and dice your data as well.

 

Note : in 2nd table you do not have sales org detail col.

 

@HarishKM Thanks for your help!

 

Will this also work if you create a line chart with the following?;

Axis = Year

Legend = Sales Organization

Value = %

 

I am afraid that the market volume for the country will be counted double in this case.

 

Could you share the PBI Desktop file with me to see how you did the bridge table?

 

Thanks a lot for the effort!

@MCBMarketing  Hey 

For bridge table . I have written a conditional col. in power query.

HarishKM_0-1618486462446.png

 

select your key col then add that a query remove duplicate and convert it as table . now connect .

to avoid duplicate number for table .use merge query to get sales org. name in your 2nd table .

 

HarishKM_1-1618486734827.png

 

And post bringing sales org in table 2 

 

HarishKM_2-1618486949858.png

 

 

View solution in original post

amitchandak
Super User IV
Super User IV

@MCBMarketing , You need to create common dimensions for

Year, Country, Sales Organization, Material and Material shape

 

Prefer star schema -https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

 

How to create such dimensions  https://www.youtube.com/watch?v=Bkf35Roman8



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak Thanks for your fast reply!

 

I have added the bridge tables, but the problem remains for the Sales Organization in Table1, that does not occur in Table2. What I am trying to accomplish is to get a line chart, which shows the market share over the years for each sales organization within the country (so the actual volume for each sales organization in Table1 needs to be divided by the total market share of the country in Table2).

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors