Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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% |
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
Hello @Infrecon
Total by region = CALCULATE(SUM(Infrecon[Sales]),ALLEXCEPT(Infrecon,Infrecon[Region]))
Market Share = SUMX(Infrecon,DIVIDE(Infrecon[Sales],[Total by region],0))
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%.
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?
@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))
replace VALUES with ALLSELECTED?? not very sure of it.
It is perhaps easier with an example
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
52 | |
27 | |
24 | |
13 | |
9 |
User | Count |
---|---|
74 | |
58 | |
47 | |
16 | |
12 |