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