Market Share by Category % BUT only show own products

[thorben]

Hi,

i created a measure "Share % per Cat" to calculate market share within a category. I used inscope and switch function to have recalculate depending on what category is in scope. This works and the dax looks like below. 

However, my data contains competitors brand as well. I only want to show my own brands for visualization reasons. If i filter only my brands the measure calculate share within only my brands but i still want the overall share within one category/group. I reckon i need to add another Inscope for my column "Own Brand or Competitor".  This column is in the data and defines binary if a sale is own brand or competitor. I am not sure how to do so.

Can you help?! 

Many Thanks
Thorben

Share % per Cat =

VAR CurrentCat = VALUES('Sales'[Cat])

VAR CurrentGroup = VALUES('Sales'[Group])

VAR RevenueForCurrentSelection = SUM('Sales'[Sales])

VAR TotalRevenue =

SWITCH(

TRUE(),

ISINSCOPE('Sales'[Brand]),CALCULATE(SUM('Sales'[Sales]),ALLSELECTED('Sales'),'Sales'[Cat] IN CurrentCat,'Sales'[Group] IN CurrentGroup),

ISINSCOPE('Sales'[Group]),CALCULATE(SUM('Sales'[Sales]),ALLSELECTED('Sales'),'Sales'[Cat] IN CurrentCat),

ISINSCOPE('Sales'[Cat]),CALCULATE(SUM('Sales'[Sales]),ALLSELECTED('Sales'))

)

RETURN

IF(

ISINSCOPE('Sales'[Cat
]),

DIVIDE(RevenueForCurrentSelection,TotalRevenue),

1

)

Output visual looks like this. However, i have multiple competitor brands that i do not want to show in the first place

[v-zhenbw-msft · Accepted solution]

Hi @thorben ,

Don’t you want to show the competitor?

Maybe you can change the ALLSELECTED to ALL. And add an own slicer.

Share % per Cat = 
VAR CurrentCat = VALUES('Sales'[Cat])
VAR _CurrentGroup = VALUES('Sales'[Group])
VAR RevenueForCurrentSelection = SUM('Sales'[Sales])
VAR TotalRevenue =
SWITCH(
TRUE(),
ISINSCOPE('Sales'[Brand]),CALCULATE(SUM('Sales'[Sales]),ALL('Sales'),'Sales'[Cat] IN CurrentCat,'Sales'[Group] IN _CurrentGroup),
ISINSCOPE('Sales'[Group]),CALCULATE(SUM('Sales'[Sales]),ALL('Sales'),'Sales'[Cat] IN CurrentCat),
ISINSCOPE('Sales'[Cat]),CALCULATE(SUM('Sales'[Sales]),ALL('Sales'))
)
RETURN
IF(
ISINSCOPE('Sales'[Cat]),
DIVIDE(RevenueForCurrentSelection,TotalRevenue),
1
)

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[thorben]

That actually worked. As simple as that 🙂 

I only want to see my own brands. Obviously i still want to see competitor brands at some point but for visuallizing reasons i needed a function to filter because there are actually many categories and brands (+100). But only a few owned brands.

Many Thanks!

[amitchandak]

@thorben , You can try allexcept or all in place of allselected and check .

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[thorben]

Allexcept does not work i guess, because it filters out again. I do not want to filter but only to show exctracts (only own brands) of the output. 

Here a sample data table (e. g. my own brand "Apple" has 55% market share in the Food Cat. Competitor Peach has 45%, however i do not want to show the competitors share in my visual)

cannot input a table here, keep getting an error. What can i do? here is a screenshot.