Forum Discussion
PIE Chart Shares
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!
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!