Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

PIE Chart Shares

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 visu...
Need Help
  • saud968's avatar
    saud968
    1 year ago

    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!

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
saud968
Memorable Member
1 year ago

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!