Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Recalculating Market Share based on selection

Hi everyone, 

 

I have a technical question. I want to know how you would best assess this issue.

Context - Market Shares: Market Shares can be calculated in many ways. If we take below table as an example, our "business world" is comprised of two manufacturers (for which we get the data). Our company (Company A) is composed of two sales manager having each 5 area managers.

 

Sales ManagerArea ManagerManufacturer20192020
Sales Manager 1Area Manager 1Company A32926
Sales Manager 1Area Manager 1Company B924324
Sales Manager 1Area Manager 2Company A415328
Sales Manager 1Area Manager 2Company B369299
Sales Manager 1Area Manager 3Company A307551
Sales Manager 1Area Manager 3Company B813796
Sales Manager 1Area Manager 4Company A913425
Sales Manager 1Area Manager 4Company B846270
Sales Manager 1Area Manager 5Company A608192
Sales Manager 1Area Manager 5Company B996268
Sales Manager 2Area Manager 6Company A193783
Sales Manager 2Area Manager 6Company B144130
Sales Manager 2Area Manager 7Company A920704
Sales Manager 2Area Manager 7Company B6436
Sales Manager 2Area Manager 8Company A529846
Sales Manager 2Area Manager 8Company B75841
Sales Manager 2Area Manager 9Company A732629
Sales Manager 2Area Manager 9Company B757738
Sales Manager 2Area Manager 10Company A241863
Sales Manager 2Area Manager 10Company B231272

We can calculate many different Market Shares:

1. Company market Share NATIONAL LEVEL: which would give 48..8% for 2019 and 61.11% for 2020

 

Company A48,38%61,11%
Company B51,62%38,89%

2.a: If we now add the Sales Manager to the table (pivot) we will see the contribution  of each sales manager accross the National Market Share:

COMPANY ASales Manager 122,5%23,7%
COMPANY ASales Manager 225,9%37,4%
COMPANY ACOMPANY A TOTAL48,4%61,1%
COMPANY BSales Manager 139,1%19,2%
COMPANY BSales Manager 212,6%19,7%
COMPANY BCOMPANY B TOTAL51,6%38,9%

2.b: we can see the market share within each sales manager:

 

Sales Manager 1Company A36,6%23,7%
Sales Manager 1Company B63,4%19,2%
Sales Manager 1 Total 100,0%42,9%
Sales Manager 2Company A67,3%37,4%
Sales Manager 2Company B32,7%19,7%
Sales Manager 2 Total 100,0%57,1%

 

3a.3b the same can again be applied on area manager level where you can see contribution to national & sales manager level.  As well as the Market Share within the area manager level.

 

For this example we can thus calculate sevaeral market shares .... My question is the following:

- I want to achieve a dashboard style where based on slicers  (National - Sales Manager level - Area manager) choice (each being able to choose independetly)  create a ONE VIEW Dashboard with gauges

The view above is in Sales and works fine with all the slicers ... for market share I have many measures, but don't know how to make them appear dinamically based on three slicers selection.

 

Anyone a suggestion?

Thanks a lot

Thibault

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    HI amitchandak 

     

    I found just the right solution actually (I believe and checked).

    Market Share (whatever the context is always calculated by doijg = sum of sales of a part / sum of sales of all parts.

    We know that calculate(sum(sales)) will be (if we let him) influenced by the "slicers".

    The question is thus how we make a measure that defines the ALL PARTS segment, as we want that all part to be dynamic based on selection. That's where it hit me:

     

    USE ALLSELECTED on the table you allow the recalculation to be done. And it works ... The total of all parts is recalculated based on filter selection.

     

    What's your opinion on this?

    Thanks a bunch.

5 Replies