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

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!

3 Replies

  • saud968's avatar
    saud968
    Memorable Member
    1 year ago

    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!


  • 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!