Comparison Market share only in specific sectors

[Infrecon]

Have the following initial situation:

A table with various sales, here is a small excerpt:

ID	Customer	Cat	Region	Sales
1	C_1	A	R_1	2
2	C_2	A	R_1	4
3	C_1	V	R_2	1
4	C_3	C	R_3	5
5	C_5	D	R_4	6
6	C_4	E	R_2	2

Now I want to filter so that the following table is displayed:

1. he should only take all the rows where my customer, in the example C_1, has made sales and then compare them with the market volume in the region.

Example: ID 1 and ID 2 both in the region R_1, C_1 has sold 2, but a total of 6 units were sold in R_1.
R_3, for example, may not appear because in our example C_1 did not sell in R_3.

Region	Market Share
R_1	33.3%
R_2	50%

[Infrecon]

Let's start again from the beginning for a moment. Sorry for the questions. I'm slowly getting closer, but not yet final.

Attached a test pbix with dummy data, which is remarkably similar to my data.

Initial situation:

I have a database with many cases per hospital and department, including the corresponding DRGs and regions (states from which the patient came).

In our example, there are three hospitals A-C, with departments of surgery or internal medicine.

On my dashboard, there should be a graphic that highlights the states (top left).

At the bottom it has a slicer by department, which currently only looks at hospital A. (we are looking at hospital A as a client and hospitals B and C as benchmarks.

On the top right it currently has a table to overview the data, but should show a table with the respective hospital and the number of cases per hospital.

Question:

Now I would have to select the surgery (in the slicer), then it should give me
1. show the market shares of hospital A (slicer, in this case surgery) on the map compared to the benchmark per state.
- In order for me to compare the departments, it is important that we take the DRGs, as a homogeneous patient population.

Now comes the tricky part. As an example, let's take DRG A01, which is present 3 times in surgery, but also 1 time in internal medicine in hospital A.

If I now want to display the states, the market share for A01 in the state of PA would have to be 66% (2 from hospital A, surgery) and 1 from hospital B (in the benchmark it does not matter which department), the case from internal medicine should not be counted.

In the state of DE, 100%, since only one case of A01 occurred in this state.

2. in the table at the top right, the number of cases per hospital A-C should be displayed, but only in the states which have fields in the map at the top left.

Can anyone help me? I like to learn

[Anonymous]

Hello @Infrecon 

Total by region = CALCULATE(SUM(Infrecon[Sales]),ALLEXCEPT(Infrecon,Infrecon[Region]))

Market Share = SUMX(Infrecon,DIVIDE(Infrecon[Sales],[Total by region],0))

 

[wdx223_Daniel]

[Infrecon]

@wdx223_Daniel 

Thank you for your suggestion, I have already tried something like that.

Now a small flaw:

In your solution it shows 33% for the region R_2, but that is not correct.

Because C_1 only sells Category V in R_2, and nobody else sells Category V, which means it should be 100%.

[wdx223_Daniel]

MarketShare% = DIVIDE(SUM('Table'[Sales]),CALCULATE(SUM('Table'[Sales]),ALL('Table'[Customer]),VALUES('Table'[Cat])),0)

[Infrecon]

@wdx223_Daniel 

On the right track, now everything is right, thank you.

Another question, how can I change the values using parameters?

Assuming that the table above contains segments and other dimensions in addition to category?

[Anonymous]

@Infrecon if youre trying to see each category by its market share then use 

TOTAL by CAT = CALCULATE(SUM(Infrecon[Sales]),ALLEXCEPT(Infrecon,Infrecon[Cat]))

Market Share = SUMX(Infrecon,DIVIDE(Infrecon[Sales],[TOTAL by CAT],0))

 

[wdx223_Daniel]

replace VALUES with ALLSELECTED?? not very sure of it.

[Infrecon]

It is perhaps easier with an example

Link

I have a slicer, here in the example Store Type

Now I want to have the market shares per territory in the table above, but only where other stores also sell in the specific category.