Recalculating Market Share based on selection

[thibbos]

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 Manager	Area Manager	Manufacturer	2019	2020
Sales Manager 1	Area Manager 1	Company A	32	926
Sales Manager 1	Area Manager 1	Company B	924	324
Sales Manager 1	Area Manager 2	Company A	415	328
Sales Manager 1	Area Manager 2	Company B	369	299
Sales Manager 1	Area Manager 3	Company A	307	551
Sales Manager 1	Area Manager 3	Company B	813	796
Sales Manager 1	Area Manager 4	Company A	913	425
Sales Manager 1	Area Manager 4	Company B	846	270
Sales Manager 1	Area Manager 5	Company A	608	192
Sales Manager 1	Area Manager 5	Company B	996	268
Sales Manager 2	Area Manager 6	Company A	193	783
Sales Manager 2	Area Manager 6	Company B	144	130
Sales Manager 2	Area Manager 7	Company A	920	704
Sales Manager 2	Area Manager 7	Company B	64	36
Sales Manager 2	Area Manager 8	Company A	529	846
Sales Manager 2	Area Manager 8	Company B	75	841
Sales Manager 2	Area Manager 9	Company A	732	629
Sales Manager 2	Area Manager 9	Company B	757	738
Sales Manager 2	Area Manager 10	Company A	241	863
Sales Manager 2	Area Manager 10	Company B	231	272

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 A	48,38%	61,11%
Company B	51,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 A	Sales Manager 1	22,5%	23,7%
COMPANY A	Sales Manager 2	25,9%	37,4%
COMPANY A	COMPANY A TOTAL	48,4%	61,1%
COMPANY B	Sales Manager 1	39,1%	19,2%
COMPANY B	Sales Manager 2	12,6%	19,7%
COMPANY B	COMPANY B TOTAL	51,6%	38,9%

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

Sales Manager 1	Company A	36,6%	23,7%
Sales Manager 1	Company B	63,4%	19,2%
Sales Manager 1 Total		100,0%	42,9%
Sales Manager 2	Company A	67,3%	37,4%
Sales Manager 2	Company B	32,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

[amitchandak]

@thibbos , based on what I got, you can use measure slicer Approach.

measure slicer
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...

Isinscope, isfiltered and hasonevalue should help

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

[thibbos]

Hi Amitchandak,

I used the first link yesterday for something else. 

Basically, using inscope/hasonevalue/isfiltered/hasonefilter in combination of all the market share calculations would be your advice right?

Thanks a bunch. 

[amitchandak]

@thibbos , so basically it will be based on the filter choose to measure. typically for Guage, you should be done with isfiltered and hasonebvalue.

In the case of visual with group/axis/row, you have to use isinscope too.

My example of card - https://www.youtube.com/watch?v=hXg3kRFSGjA

[thibbos · Accepted solution]

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.

[v-lionel-msft]

Hi @thibbos ,

I think so too, thank you for sharing.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.