Market share scenario modelling depending on user selection

[Jolcyna]

Hi All,

I am struggling with the following problem in Power BI and hope you will be able to advice if there are any function, trick or maybe data structuring I should apply to achieve what I need.

I would like to see how the market share of the B2B company (market share in terms of number of clients in the specific industry). I have the following table (just mock it up as an example)

• Company name – it’s client name
• Current service provider – is the B2B service provider to the specific client (3 service providers)
• Contract expiration year – last year where the contract between two parties is valid
• First year of new service provider – first year where the new service provider will be an active business partner on the client

Company name	Sector	Country	Current Service Provider	Contract year start	Contract expiration year (last year of service)	First year of new service provider
ABC	Telecoms	United Kingdom	X	2013	2023	2024
CBA	Telecoms	Germany	Y	2017	2022	2023
BCI	Telecoms	Netherlands	Z	2016
DTW	Power	Netherlands	X	2015	2022	2023
KHB	Power	Belgium	Y	2016	2020	2021
LWI	Mining	Germany	Z	2015	2021	2022
HBI	Mining	United Kingdom	Y	2015	2020	2021
LCO	Automotive	United Kingdom	Z	2014	2023	2024

If I would like to create a report that allows to see the market share forecast in the specific years of the service providers (X,Y,Z) with an option to model the results. i.e. in 2021 the market situation will change a bit as KHB and HBI contract with service provider “Y” will expire. I would like to be able to see, how the market share will look in 2021 if these two companies (KHB, and HBI) will sign in the contract with some other service providers (i.e. KHB with service provider X and HBI will extend the contract with company Y). I know it’s tricky as it requires data modelling depending on the selection. I am wondering if that’s feasible in Power BI, if I could add any supporting table to achieve it, apply any formulas etc.

I guess it would include two main steps:

The first step would be to see probably is the selected year is in between “Contract start year” and “Contract expiration year” – we will generate the number of companies with the valid contracts with the service providers

After year selection, we would probably need to receive the list of Companies that will be open for a new service provider contract signature (with no valid contract with former service provider) and we should be able to assign different service providers to those companies (manually) to see how it will impact the market.

Ps. If the cell are empty (contract expiration date) it means that the contract type signed in is not set up and we can assume that the cooperation between those two companies will continue.

Thank you very much for any suggestions and let me know if anything unclear.

[Anonymous]

Hi 

the easiest solution would be to change the model so you have one row per year per contract.

So for example

ABC

Telecoms

United Kingdom

X

2013

2023

2024

will be made of a row for each year

ABC	Telecoms	United Kingdom	X	2013
ABC	Telecoms	United Kingdom	X	2014
ABC	Telecoms	United Kingdom	Y	2015

So in this model you can see that ABC was with X in 2013 and 2014 and switched to Y in 2015

(As far as I can see in your model there's no information of what was the "previous" provider. You know only what will be next...)

Once you have a model like this is fairly easy because each year will have a full list of providers and clients that you can slice.

You can achieve this result, starting from your dataset, using some generated tables functions like GENERATE, but first you will have to have a "date" dimension (which will be an year dimension in your case) with all possible years in the past and the future.

Then you do GENERATE(table1, yeardim). You will have to select columns using SELECTCOLUMNS, though.

Also, you'll have to manage your "empty" contract expiration by setting for example 2099 and using that as a top limit.

[Jolcyna]

Thank you adetogni! In fact, I have just current service porviders I do not know what will be the future provider. The aim is to see how the market share changes when a different service providers are selected in the future when the contract expires. Do not see yet, how the model proposed could help? i.e. for ABC is expire 2023 so the market share for 2024 will be different as ABC can sign up a contract with company Y or company Z.. Thanks in advance for any further ideas:-)

[Anonymous]

Hi

with this model is easy to see how the market share will be when a contract will expire because if you select year 2021 when a contract expire there won't be a row for that client and so the other providers will share the amount of 100% of the market.

The question is how you want to model the what-if. You want to simulate 

 I would like to be able to see, how the market share will look in 2021 if these two companies (KHB, and HBI) will sign in the contract with some other service providers (i.e. KHB with service provider X and HBI will extend the contract with company Y

So you need to have the option to select each and every company and associate to any possible vendor (or NO vendor) for any possible year?

Depending on the amount of companies, years and providers, you will have a big number of combinations: Pbi does not allow you to "directly assign" a company to a provider. PBI allows you to SELECT a row from a table, and that row must exist.

So for example you canNOT have a sort of an excel where you have all the companies in the left column and you can type the new provider in the right column, and make all the simulations. That's not the way it works. 

Maybe it's better to think at the solution: how do you think someone would enter the "combinations"?

[Anonymous]

sorry, addendum: i worked only at the modeling part, haven't had time to look at your what-if simulation.