Forum Discussion
Pie Chart Grouped By Ranking
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
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
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
7 Replies
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
Hello Anonymous
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 _tableFurthermore, 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
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
