Filtering

[Anonymous]

HI!

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!

[christianfcbmx]

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.

[Anonymous]

Hi, @christianfcbmx !!!

There are two files:

pbix

excel

[Anonymous]

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

[christianfcbmx]

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

SHARE% = CALCULATE(SUM(DB[VOLUME]);
FILTER(db;DB[MONTH]=DB[MONTH]))
/CALCULATE(SUM(DB[VOLUME]);ALL(DB[MONTH]);ALL(DB[PRODUCER]);ALL(DB[CATEGORY]);ALL(DB[SubcATEGORY]))

[Anonymous]

@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?

[christianfcbmx]

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

🙂

[Anonymous]

@christianfcbmx 

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.