Pie Chart Grouped By Ranking

[Andvil]

Hello to everyone,

I am trying to group a pie chart of many courier company sales considering the following aspects:

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

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

I will be extremely grateful for your help, I have been stuck in this issue for a while.

Best, 

Jalv

[v-yiruan-msft · Accepted solution]

Hi @Andvil ,

Actually, what that user created is calculated table not measure or calculated column. If you used his provided formula with calculated table, it works well. Please check the below screen shot. 

In addition, there is another method: create 5 different measure to display the summarized sales of these 5 five partitions.

You can refer the details of both methods in this link.

Best Regards

Rena

[v-yiruan-msft]

Hi @Andvil ,

Could you please provide some sample data? The pie chart will be divided into 5 parts with these values just like below screen shot? Whether the following screen shot is your expected result? 

1. Company X

2. Company Y

3. Company Z

4. Top 5 companies except company X,Y and Z

5. The left companies

Best Regards

Rena

[Andvil]

Hello @v-yiruan-msft 

Another user helped me with this formula, but when using it in the original report, I receive the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"

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

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 filter packages and the pie chart updates with the top 5 regarding packages.

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

Thank you very much for your help.

Best,

Jose Lopez

[v-yiruan-msft]

Hello @jlopezentregas ,

Actually, what that user created is the calculatedtab, the column is not measured or calculated. If you used your formula provided with calculated table, it works well. Please check the following screenshot.

In addition, there is another method: create 5 different measures to show the summarized sales of these 5 five partitions.

You can see the details of both methods in this link.

Best regards

Rena

[v-yiruan-msft · Accepted solution]

Hi @Andvil ,

Actually, what that user created is calculated table not measure or calculated column. If you used his provided formula with calculated table, it works well. Please check the below screen shot. 

In addition, there is another method: create 5 different measure to display the summarized sales of these 5 five partitions.

You can refer the details of both methods in this link.

Best Regards

Rena

[Andvil]

Hi @v-yiruan-msft ,

Thank you for your answer! Now I was able to create the calculated table, thank you for the advice. But, when I create the pie chart it does not show the 5 slices. It only shows the "other" group.

Thank you very much for all the comments and help provided.

Best,

Jose L

[v-yiruan-msft · Accepted solution]

Hi @Andvil ,

As checked the formula which you created in your report file, it is CORRECT.  And why those specific 3 companies didn't display in Pie Chart besides others and top 5 companies, it is due to companies "ENTREGAS ESPECIALES ESPENTREGAS S.A.", "DHL EXPRESS ECUADOR S.A." and "LAARCOURIER EXPRESS S.A." with very small proportion as compared with others and top 5 companies group. So they cannot be displayed on Pie Chart. Maybe you can use other visual(for example: clustered column chart etc. ) to represent the proportion just like below screen shot.

Best Regards

Rena

[amitchandak]

@Andvil ,

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

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