Forum Discussion

Andvil's avatar
Andvil
Icon for Helper V rankHelper 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 res...
  • 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.

v-yingjl's avatar
v-yingjl
Icon for Community Support rankCommunity 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
    Icon for Helper V rankHelper 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
      Icon for Community Support rankCommunity 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.

      • Andvil's avatar
        Andvil
        Icon for Helper V rankHelper V
        6 years ago

        HI v-yingjl,

         

        I did what you told me and finally I didn´t have any mistake in the formula to create the table. But, when creating the pie chart, it only shows the "Others" categories. I am attaching the report that I am using, can you please help me checking my mistakes?

        Pie Chart Top N Rank Example 

         

        Otherwise, thank you very much for all the help you have provided previously.

         

        Best,

         

        Jose L

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

      Hi Andvil ,

      Sorry fot that I cannot clearly explain the expression previously. Please try to use this expression to create a calculate table not a measure and you will get the right result.

      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.