topic Re: DAX formula - calculate market share, compatible with filters and in charts showing categories in DAX Commands and Tips

DAX formula - calculate market share, compatible with filters and in charts showing categories

dp_75 — Fri, 25 Oct 2024 14:27:51 GMT

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 ?

MktShare = VAR Volume =

COUNT (sample data_[Price])

VAR AllVolume =

CALCULATE ( COUNT ( sample data_[Price]), ALL(sample data))

RETURN

DIVIDE ( Volume, AllVolume )

https://www.dropbox.com/scl/fi/yrlgyw929d250g5zbmfdt/sample-data.xlsx?rlkey=b8tv3vg4717ea85fckj2q9a16&st=6mfzoz3p&dl=0

thanks in advance for any help you can give !

dp_75

Re: DAX formula - calculate market share, compatible with filters and in charts showing categories

lbendlin — Fri, 25 Oct 2024 22:05:32 GMT

For most scenarios you can use implicit measures

Or you can use ALLSELECTED

Re: DAX formula - calculate market share, compatible with filters and in charts showing categories

dp_75 — Sat, 26 Oct 2024 08:10:38 GMT

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

Re: DAX formula - calculate market share, compatible with filters and in charts showing categories

lbendlin — Sat, 26 Oct 2024 13:37:51 GMT

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?

Re: DAX formula - calculate market share, compatible with filters and in charts showing categories

dp_75 — Tue, 29 Oct 2024 11:28:05 GMT

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

Re: DAX formula - calculate market share, compatible with filters and in charts showing categories

lbendlin — Wed, 30 Oct 2024 00:11:23 GMT

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.

Re: DAX formula - calculate market share, compatible with filters and in charts showing categories

dp_75 — Wed, 30 Oct 2024 10:42:43 GMT

Thanks for the reply ibendlin,

See the .pbix file in link below:

https://www.dropbox.com/scl/fi/zsev7d2tvqy1v8rmsmahh/241030-Sample.pbix?rlkey=eywsmypqmiyx75thoj8l1ga37&st=404e6xtf&dl=0

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

Re: DAX formula - calculate market share, compatible with filters and in charts showing categories

Anonymous — Fri, 01 Nov 2024 08:48:56 GMT

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.

Re: DAX formula - calculate market share, compatible with filters and in charts showing categories

DemoFour — Fri, 01 Nov 2024 09:38:38 GMT

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.

Re: DAX formula - calculate market share, compatible with filters and in charts showing categories

dp_75 — Sat, 02 Nov 2024 11:42:50 GMT

Thanks all - using removefilters in a new DAX measure solves the market share question.

dp_75