Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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
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/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@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.
@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.
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
@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
@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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |