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

[MCBMarketing]

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:

Year	Country	Sales Organization	Material	Material shape	Actual volume
2019	Netherlands	A	Wood	Planks	200
2020	Netherlands	A	Wood	Planks	190
2019	Netherlands	B	Wood	Planks	20
2020	Netherlands	B	Wood	Planks	18

Table 2 with market volume:

Year	Country	Material	Material shape	Market volume
2019	Netherlands	Wood	Planks	950
2020	Netherlands	Wood	Planks	800

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!

[Jihwan_Kim]

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.

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]

@MCBMarketing  Hey 

You can refer below image 

Schema Model 

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.

[MCBMarketing]

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

[HarishKM · Accepted solution]

@MCBMarketing  Hey 

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

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 .

And post bringing sales org in table 2 

[amitchandak]

@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

[MCBMarketing]

@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).