Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi there,
I'm trying to calculate a field for Company Market Share, that can be put into visuals and filtered bby category.
See sample data attached (link below).
I'd like to have a formula that can be used in a chart, and for a filtered company - show market share by post code, by price band etc
I've been using bthe fomula below, but its throwing out incorrect values ?
thanks in advance for any help you can give !
dp_75
Solved! Go to Solution.
Thanks for the reply from lbendlin.
Hi @dp_75 ,
You can create a measure, as described by lbendlin, and use the REMOVEFILTERS function or the ALL function to remove the filter from the Company field to get the target result.
Mktshare(%) =
DIVIDE(
SUM(Sheet1[Price]), ---Total price for the selected company
CALCULATE(SUM(Sheet1[Price]),REMOVEFILTERS(Sheet1[Company])) --- Total price for all companies
)
The final result is as follows, hopefully it will meet your needs.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks all - using removefilters in a new DAX measure solves the market share question.
dp_75
Thanks for the reply from lbendlin.
Hi @dp_75 ,
You can create a measure, as described by lbendlin, and use the REMOVEFILTERS function or the ALL function to remove the filter from the Company field to get the target result.
Mktshare(%) =
DIVIDE(
SUM(Sheet1[Price]), ---Total price for the selected company
CALCULATE(SUM(Sheet1[Price]),REMOVEFILTERS(Sheet1[Company])) --- Total price for all companies
)
The final result is as follows, hopefully it will meet your needs.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
For most scenarios you can use implicit measures
Or you can use ALLSELECTED
Thanks Ibendlin,
If you have a filter for companyh on the page those methods don;t appear tp bring back the correct answers - see pic below.
Any ideas for fixing this ?
Many thanks
dp_75
for a filtered company - show market share by post code, by price band etc
If you filter for a company in the same table then you will only get the data for that company, resulting in 100% market share. Please be more explicit in what you expect to see - Do you want to compare the selected company against all other companies?
Hi Ibendlin, yes that's correct I'm trying to show the Market share of a company (relative to total all companies), by postcode, by price tier and otehr categories. I have a filter on the page for the companyh in question (See pic i sent earleir in thread). Do i need to create a DAX measure for this ? or can it be done via the implicit measuires you suggested ? Thanks again dp_75
Depends on what else is happening in your data model.
(relative to total all companies), by postcode, by price tier and otehr categories.
Sounds like you want to REMOVEFILTERS on the company, but keep all other filters intact.
Thanks for the reply ibendlin,
See the .pbix file in link below:
The screen shot of Sheet 2 below shows the chart I am trying to show created using a % of Total. You can see the reported market share for company A as a % of all SW18 is incorrect in the chart - it should be 16.8%, not 11.6% as shown in the chart.
I think the problem is we are expressing a share of total as defined by filters and by the x-axis in the chart ? Any ideas on how to fix this ?
Many thanks
dp_75
Hay @dp_75
I have had a look at your data and I have a few questions as the results from the DAX posted above would help you solve your problem.
Why in your first post are you counting the Price instead of summing the Price?
I have made a quick mock up for you, but you need to look at the numbers.
If you want to make your data more robust I would suggest building a data model and not a flat file for your report. This will help with creating the DAX and then creating the visuals for your metrics.
This way you can use the dimensions against the measures to filter your data model and you can then choose the way you filter this with DAX or dragging and dropping your data model on to the canvas.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
15 | |
14 | |
12 | |
9 |