Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
dp_75
Frequent Visitor

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

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=b8tv3vg4717ea85fckj2q9a1...

 

thanks in advance for any help you can give !

dp_75

1 ACCEPTED SOLUTION
v-denglli-msft
Community Support
Community Support

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.

vdengllimsft_0-1730450719982.png

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.

View solution in original post

9 REPLIES 9
dp_75
Frequent Visitor

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

dp_75 

v-denglli-msft
Community Support
Community Support

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.

vdengllimsft_0-1730450719982.png

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.

lbendlin
Super User
Super User

For most scenarios you can use implicit measures

lbendlin_0-1729893784389.png

Or you can use ALLSELECTED

 

lbendlin_1-1729893924992.png

 

 

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.

dp_75_0-1729930206073.png

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:

https://www.dropbox.com/scl/fi/zsev7d2tvqy1v8rmsmahh/241030-Sample.pbix?rlkey=eywsmypqmiyx75thoj8l1g...

 

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.

 

dp_75_0-1730284445380.png

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

DemoFour
Responsive Resident
Responsive Resident

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. 

DemoFour_0-1730453505267.png

DemoFour_1-1730453727881.png

 



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. 





Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors