Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I am looking for help with creating a measure for calculating the market penetration.
I have the data below, which shows the actual volume per country, per sales organization, per year, per material, per material shape. The Market volume, however, is the total market volume for the country. The formula for Market penetration = DIVIDE( [Actual volume], [Market volume], "" ).
Country | Sales Organization | Year | Material | Material shape | Actual volume | Market volume | Market penetration |
Netherlands | A | 2020 | Plastic | Bars | 2.400236104 | 40.4 | 5.9% |
Netherlands | A | 2020 | Plastic | Sheets | 0.926026057 | 102.3 | 0.9% |
Netherlands | B | 2020 | Plastic | Bars | 3.979754703 | 40.4 | 9.9% |
Netherlands | B | 2020 | Plastic | Sheets | 39.27918723 | 102.3 | 38.4% |
Netherlands | C | 2020 | Plastic | Bars | 0.269531685 | 40.4 | 0.7% |
Netherlands | C | 2020 | Plastic | Sheets | 0.601579538 | 102.3 | 0.6% |
Netherlands | A | 2020 | Wood | Bars | 4.428251924 | 268.1 | 1.7% |
Netherlands | A | 2020 | Wood | Sheets | 2.689547184 | 813.5 | 0.3% |
Netherlands | B | 2020 | Wood | Bars | 21.11403556 | 268.1 | 7.9% |
Netherlands | B | 2020 | Wood | Sheets | 190.302465 | 813.5 | 23.4% |
Netherlands | C | 2020 | Wood | Bars | 0.332333771 | 268.1 | 0.1% |
Netherlands | C | 2020 | Wood | Sheets | 0.602871471 | 813.5 | 0.1% |
Now this is the result I expect for market penetration, since this is the deepest drill-down I can do with this data, but if I plot this on a higher level without the sales organization included, the market share sums up by the amount of the sales organizations within the country (by 3 in this case). Thus, my actual volume gets divided by 3 times the market volume, instead of only once.
Country | Year | Material | Material shape | Actual volume | Market volume | Market penetration | Expected value |
Netherlands | 2020 | Plastic | Bars | 6.649522492 | 121.2 | 5.50% | 16.50% |
Netherlands | 2020 | Plastic | Sheets | 40.80679283 | 306.9 | 13.30% | 39.90% |
Netherlands | 2020 | Wood | Bars | 25.87462125 | 804.3 | 3.20% | 9.70% |
Netherlands | 2020 | Wood | Sheets | 193.5948837 | 2440.5 | 7.90% | 23.80% |
In this table the Market volume shows triple the value it should, since there are 3 sales organizations within this country.
Can someone help me to create a measure that takes all conditions into account?
Thanks a lot!
Solved! Go to Solution.
Hi, @MCBMarketing
Please check the below picture and the sample pbix file's link down below.
All measures are in the sample pbix file.
Also, when you insert Sales Org. Column, it still shows the correct result.
https://www.dropbox.com/s/le76k9l6pp1lv9d/mcbmarketing.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: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
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.
Hi any chance you could help me with this one?
Business area | REGS | Sales new used | penetration |
|
A | 200 | New | (Count of new) ‘New’/REGS |
|
B | 300 | New |
|
|
C | 453 | New |
|
|
A | 200 | Used |
|
|
B | 234 | Used |
|
|
C | 754 | New |
|
|
A | 768 | New |
|
|
Hi, @MCBMarketing
Please check the below picture and the sample pbix file's link down below.
All measures are in the sample pbix file.
Also, when you insert Sales Org. Column, it still shows the correct result.
https://www.dropbox.com/s/le76k9l6pp1lv9d/mcbmarketing.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: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
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.
This did it, thanks a lot!
I was struggling in the measure for the market penetration itself, not thinking about adjusting the market volume measure.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
103 | |
98 | |
98 | |
38 | |
37 |
User | Count |
---|---|
154 | |
120 | |
73 | |
73 | |
63 |