PIE Chart Shares

[Anonymous]

Hello

I want to show Market Share of Sub Category but since a particular category can have many sub categories i want to only show top 5-6 sub category on a pie chart. The problem is if i use visual level filters my values changes and does not show original markset share.
I need a help where based on slicers selection market share is calculated but on pie chart or bar chart i should be able to show top 5 sub category only with original market share.

[Anonymous]

@saud968 Thank you so much for the help,
Just one clarification regarding ALL Except 
My Slicers on the Page are :- Category,Market,Facts(Sales/Volume),Period(MAT,12WK,4WK)
I am assuming that when shares for sub category are being calculated it is taking all the above slicers into considerations or is it only taking category?
If i have to take everything into consideration i believe we have to create unique identifiers ?

[saud968 · Accepted solution]

You're welcome! Let's clarify how to ensure all slicers are considered when calculating the market share for subcategories.

Using ALL and ALLSELECTED in DAX
When you use the ALL function, it removes all filters from the specified columns or tables. However, if you want to keep certain slicers in consideration, you should use the ALLSELECTED function instead. This function respects the filters applied by slicers on the report page.

Adjusting the Market Share Calculation
To ensure that all your slicers (Category, Market, Facts, Period) are taken into consideration, you can modify the market share measure to use ALLSELECTED:

Market Share =
DIVIDE(
SUM('Sales'[SalesAmount]),
CALCULATE(SUM('Sales'[SalesAmount]), ALLSELECTED('Sales'))
)
Creating Unique Identifiers
If you need to ensure that each combination of slicer selections is unique, you can create a unique identifier by concatenating the relevant columns. This can be done in a calculated column or measure:

Unique Identifier =
'Category'[CategoryName] & "-" &
'Market'[MarketName] & "-" &
'Facts'[FactType] & "-" &
'Period'[PeriodType]
Example with All Slicers Considered
Here’s how you can adjust the measures to ensure all slicers are considered:

Market Share Measure:

Market Share =
DIVIDE(
SUM('Sales'[SalesAmount]),
CALCULATE(SUM('Sales'[SalesAmount]), ALLSELECTED('Sales'))
)
Ranking Measure:

Subcategory Rank =
RANKX(
ALLSELECTED('Subcategory'),
[Market Share],
,
DESC,
Dense
)
Top Subcategories Measure:

Top Subcategories =
IF(
[Subcategory Rank] <= 5,
'Subcategory'[SubcategoryName],
"Other"
)
By using ALLSELECTED, you ensure that the market share calculation respects all the slicers on your report page. This way, the market share values will be accurate based on the current slicer selections.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[saud968]

You have a dataset with multiple categories, each with many subcategories. You want to:

Filter the data based on slicers to show specific categories and their subcategories.
Calculate the market share of each subcategory within its category.
Display only the top 5-6 subcategories on a pie chart, ensuring their market share is calculated based on the total market share of the category, not just the filtered data.
Solution Approach

To achieve this, we'll leverage a combination of DAX measures and visual level filters.

1. Create a Measure for Market Share

Market Share =
DIVIDE(
SUM(YourMeasure),
CALCULATE(
SUM(YourMeasure),
ALLEXCEPT(YourTable, YourCategoryColumn)
)
)

Replace YourMeasure with the measure representing the value you want to calculate the market share for (e.g., sales, revenue, units sold).

2. Create a Measure for Rank

Rank =
RANKX(
ALLEXCEPT(YourTable, YourCategoryColumn, YourSubCategoryColumn),
[Market Share],
DESC,
SKIP
)

3. Create a Visual Level Filter

Add the Rank measure to a visual level filter.
Set the filter condition to Rank <= 5.
4. Create the Pie Chart

Add YourSubCategoryColumn to the Angle field.
Add Market Share to the Size field.
Apply the visual level filter created in step 3.
Key Points:

ALLEXCEPT Function: This function removes all filters from the specified columns except for the category column. This ensures that the market share is calculated based on the total category value, not just the filtered data.
RANKX Function: This function ranks the subcategories within each category based on their market share.
Visual Level Filter: This filter limits the number of subcategories displayed on the chart to the top 5-6, while still calculating their market share based on the total category value.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!