Pie chart grouped by classification

[Andvil]

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 · Accepted solution]

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]

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]

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 · Accepted solution]

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]

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 · Accepted solution]

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]

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.

[amitchandak]

@Andvil ,

Refer if this can help:https://www.daxpatterns.com/dynamic-segmentation/

https://www.daxpatterns.com/static-segmentation/

[Andvil]

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.