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

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.

  • Andvil's avatar
    Andvil
    Helper 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
      Community Support
      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.