Forum Discussion
Pie chart grouped by classification
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 _groupThen 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 LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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 _groupAnd you can put your category column in the pie chart visual filter to filter each category:
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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
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.