cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filtering

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!
Capture.PNG
7 REPLIES 7
christianfcbmx
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.

 

 

 

Anonymous
Not applicable

Hi, @christianfcbmx !!!

 

There are two files:

pbix

excel

Anonymous
Not applicable

ex1.png

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

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

ex2.PNG

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

 

🙂

Anonymous
Not applicable

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

ex2.PNG

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.