cancel
Showing results for
Did you mean:
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:

 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!
1 ACCEPTED SOLUTION
Responsive Resident

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

6 REPLIES 6
Community Champion

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.

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.

Responsive Resident

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.

New Member

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

Responsive Resident

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

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!

New Member

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

Announcements

#### Welcome to the User Group Public Preview

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

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

#### Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors