Forum Discussion

Andvil's avatar
Andvil
Helper V
6 years ago
Solved

Pie chart grouped by classification

Hello everyone

I'm trying to group a pie chart of many sales of courier companies taking into account the following aspects:

  • Company X, Company Y and Company Z, which are the top 2, 4 and 5 respectively, must have their own independent portion
  • Group the top 5 companies into another slice, this slice does not include Company X, Y and Z, as each of them must have its own slice. Labeling it as "Other Top 5 Messengers"
  • Group the other companies into another slice under the label "Other"

Therefore, I would have 5 different sectors in my pie chart and every month I update my database, the pie chart will also update the top 5 messengers according to their sales.

I'll be very grateful for your help, I've been stuck on this problem for a while.

Best

Jalv

  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi Andvil ,

    About group by category, I add a category column in the table like this to create group:

    The create a calculate column use the similar formula as I first posted:

    Group =
VAR tab =
    FILTER (
        SUMMARIZE ( 'Table', 'Table'[Company], "Sales", SUM ( 'Table'[Sales] ) ),
        NOT ( [Company] IN { "Company X", "Company Y", "Company Z" } )
    )
VAR newtab =
    ADDCOLUMNS (
        tab,
        "Rank",
        VAR _category = 'Table'[Category]
        RETURN
            RANKX ( FILTER ( 'Table', 'Table'[Category] = _category ), [Sales] )
    )
VAR _group =
    VAR _company = [Company]
    RETURN
        IF (
            _company IN { "Company Z", "Company Y", "Company X" },
            [Company],
            VAR x =
                SUMX ( FILTER ( newtab, [Company] = _company ), [Rank] )
            RETURN
                IF ( x >= 1 && x <= 5, "Ohter Top 5 Countries", "Others" )
        )
RETURN
    _group

    Then you can create a pie chart and use category column as a slicer:

    Here is the sample file about group by category that hopes also help you, please try it: Rank and group by category.pbix 

    Best Regards,
    Yingjie Li

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

  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi Andvil ,

    The rank table is used by group without category as a reference. The real 'Category' column is in your 'MRN_2020' table, so you should create the 'Group' calculate column in 'MRN_2020' table by using previous formula:

    Group = 
VAR tab =
    FILTER (
        SUMMARIZE ( MRN_2020, MRN_2020[Cargo Company], "KILOS", SUM( MRN_2020[Kilos] ) ),
        NOT (MRN_2020[Cargo Company] IN { "ENTREGAS ESPECIALES ESPENTREGAS S.A.", "DHL EXPRESS ECUADOR S.A.", "LAARCOURIER EXPRESS S.A." } )
    )
VAR newtab =
    ADDCOLUMNS (
        tab,
        "Rank",
        VAR _category = 'MRN_2020'[Category]
        RETURN
            RANKX ( FILTER ( 'MRN_2020', 'MRN_2020'[Category] = _category ), [KILOS],,DESC,Dense )
    )
VAR _group =
    VAR _company = 'MRN_2020'[Cargo Company]
    RETURN
        IF (
            _company IN { "ENTREGAS ESPECIALES ESPENTREGAS S.A.", "DHL EXPRESS ECUADOR S.A.", "LAARCOURIER EXPRESS S.A." },
            [Cargo Company],
            VAR x =
                SUMX ( FILTER ( newtab, [Cargo Company]= _company ), [Rank] )
            RETURN
                IF ( x >= 1 && x <= 5, "Other Top 5 Countries", "Others" )
        )
RETURN
    _group

    And you can put your category column in the pie chart visual filter to filter each category:

     

    Best Regards,
    Yingjie Li

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

8 Replies

    • Andvil's avatar
      Andvil
      Helper V
      6 years ago

      I am new in Dax formulas so i I could not figure out the solution. I dont know what im doing wrong and also i do not have min and max values, as sales data are updated on a monthly basis and vary a lot.

       

      I would like to attach a link of an exaple of what I am trying to do in order to receive further help: TOP N BY RANKING EXAMPLE LINK 

       

      Thank you in advance for any help or additional advice.

       

       

  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    Hi Andvil ,

    You can try this measure to create a calculate table:

    Rank table =
VAR tab =
    FILTER (
        SUMMARIZE ( 'Table', 'Table'[Company], "Sales", SUM ( 'Table'[Sales] ) ),
        NOT ( [Company] IN { "Company X", "Company Y", "Company Z" } )
    )
VAR newtab =
    ADDCOLUMNS ( tab, "Rank", RANKX ( tab, [Sales] ) )
VAR _table =
    SUMMARIZE (
        'Table',
        'Table'[Company],
        "Sales", SUM ( 'Table'[Sales] ),
        "Group",
        VAR _company = [Company]
        RETURN
            IF (
                _company IN { "Company Z", "Company Y", "Company X" },
                [Company],
                VAR x =
                    SUMX ( FILTER ( newtab, [Company] = _company ), [Rank] )
                RETURN
                    IF ( x >= 1 && x <= 5, "Ohter Top 5 Countries", "Others" )
            )
    )
RETURN
    _table

    The rank table will like this:

    Put the group column and the sales column in the pie chart and the final result is like on the right of the report:

    Here is my sample file that hopes to help you, please try it: Pie chart grouped by classification 

     

    Best Regards,
    Yingjie Li

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

    • Andvil's avatar
      Andvil
      Helper V
      6 years ago

      Hi v-yingjl ,

       

      First of all, thank you for replying and helping me, that is exactly what I am trying to do. Unfortunately, I copied the measure in my original report but I received the following error: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value". Why am I getting this error and how can I solve it?

       

      Furthermore, the next thing I would like to do is filter according 2 different categories. For example, I want to have the pie chart where I can filter according to "documents" or "packages". If I select the filter "documents", the pie chart calculates the top 5 companies with more sales in the category Documents, and if I want to display the market share regarding only packages, then I select filter packages and the pie chart updates.

       

      It would be the same excercise but adding the option to separate or filter between two categories.

       

      Best,

       

      Jose Lopez

      • v-yingjl's avatar
        v-yingjl
        Community Support
        6 years ago

        Hi Andvil ,

        About group by category, I add a category column in the table like this to create group:

        The create a calculate column use the similar formula as I first posted:

        Group =
VAR tab =
    FILTER (
        SUMMARIZE ( 'Table', 'Table'[Company], "Sales", SUM ( 'Table'[Sales] ) ),
        NOT ( [Company] IN { "Company X", "Company Y", "Company Z" } )
    )
VAR newtab =
    ADDCOLUMNS (
        tab,
        "Rank",
        VAR _category = 'Table'[Category]
        RETURN
            RANKX ( FILTER ( 'Table', 'Table'[Category] = _category ), [Sales] )
    )
VAR _group =
    VAR _company = [Company]
    RETURN
        IF (
            _company IN { "Company Z", "Company Y", "Company X" },
            [Company],
            VAR x =
                SUMX ( FILTER ( newtab, [Company] = _company ), [Rank] )
            RETURN
                IF ( x >= 1 && x <= 5, "Ohter Top 5 Countries", "Others" )
        )
RETURN
    _group

        Then you can create a pie chart and use category column as a slicer:

        Here is the sample file about group by category that hopes also help you, please try it: Rank and group by category.pbix 

        Best Regards,
        Yingjie Li

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