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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Not applicable




Could you please help me with filtering:


I have theese measures: Country; Producer; Category; SubCategory; Month.


I have calculated Market Share of Producers, where I take for base 1Month and all brands.

SHARE%  = CALCULATE(SUM(db[Volume]);FILTER(db;db[Month]=db[Month]))/CALCULATE(SUM(db[Volume]);ALL(db[Month];db[Producer]))
To show TOP 5 Producers I put in TOP N measure share%.
So on Chart I see top 5 Producers Market Share by Months.
If I want to see top5 Producers' shares in certain Category, I choose Category I need and than I see top5 Producers' market share (for 100% base it takes data slice 1month&all producers&Category).
Problem appears when I choose only some categories. In this case BI takes for total base (I mean base on which I devide separate Producers sales, this part of measure - CALCULATE(SUM(db[Volume]);ALL(db[Month];db[Producer])) ) calculation volume of segments where are sales of top5 producers - not full sales of all theese categories (there are not all categories i have chosen in filter).
Maybe there is a problem with the formula? Or how can I calculate ALL sales in chosen Categories for TOP5 Producers, even if they are not in theese categories (no sales)?
Thank you!
Post Patron
Post Patron

Hi @Anonymous 


Can you give us an example where we can see the wrong result and what is the expected right result?


Also if you share your pbix file would be nice.




Not applicable

Hi, @christianfcbmx !!!


There are two files:



Not applicable


The difference appears because there is no Bar Category in TOP5 Producers. 

@Anonymous Try that one and tell me if it works.


Not applicable

@christianfcbmx ,


this formula works great, if we want to see the share from the full data base - I will save this formula, thank you. 

But in my example full data base (100%) changes after choosing filters, for example, I choose bar and cone categories or just cone category - in all theese cases it changes base.


Maybe you have another Ideas? Smiley Happy

mmmm...give me more details or draw me something with the expected result ....for example if we filter by cone...what do you expect to have as a result



Not applicable


Lets take volume of December. I choose Cone (261kg) and Bar (17.5kg) categories - sum is 279kg. So the share of O1 is 44% (123.9/279=0.44) etc.

In your formula it takes full sales of all producers in december (1777kg) - the share of cone and bar categories is 6.9% (123.9kg/1777kg).

In mu current verision it takes only 261kg for cone and bar categories, because top 4 producers do not sell bar ice cream.



Helpful resources

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.