Create Category based on product % share

Hi Anno2019 ,

First create a table and slicer:

Then create a measure for Generate Series:

Gengerate Series  = MIN('For slicer'[Value])

Here are two way to solve your problem:

1. create a measure:

Category =

var _a = MAXX( FILTER('Table','Table'[Sales Person Name]=MAX([Sales Person Name])),[Apples Share %])> [Gengerate Series]

var _p = MAXX( FILTER('Table','Table'[Sales Person Name]=MAX([Sales Person Name])),[Pears Share %] )> [Gengerate Series]

var _o = MAXX( FILTER('Table','Table'[Sales Person Name]=MAX([Sales Person Name])),[Oranges Share %])> [Gengerate Series]

var _l = MAXX( FILTER('Table','Table'[Sales Person Name]=MAX([Sales Person Name])),[Leeches Share %])> [Gengerate Series]

var _av = MAXX( FILTER('Table','Table'[Sales Person Name]=MAX([Sales Person Name])),[Apples Share %])> [Gengerate Series]

var _k = MAXX( FILTER('Table','Table'[Sales Person Name]=MAX([Sales Person Name])),[Kiwis Share %])> [Gengerate Series]

return IF(_a||_av||_k||_l||_o||_p,"Single Product","Multi Product")

Output:

2. Unpivot the columns in power query:

Here is the M code:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TU9BCoMwEPyKBLx5yCbdGK8tpSfBu3gIElBsI8T04O+bbKztZSaT3Z1h+p49rH8Zt7OKdTZYX3TGL9ZHCch5JJGB3k0mJMAsVI01rZQReAKZAOSpNZRsqHp2m2Znom7ncdmLdn1vNi0qANpPKIBEkymOKI1yQIPANCN3AopEfn4InYPuW1jdnKKuTzMusZILE3XS8pJO1GEpSepMNf41VY1A8fXV2f2XCUfNYfgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, #"Sales Person Name" = _t, #"Apples Sales" = _t, #"Pears Sales" = _t, #"Oranges Sales" = _t, #"Leeches Sales" = _t, #"Avocado Sales" = _t, #"Kiwis Sales" = _t, #"Total Sales" = _t, #"Apples Share %" = _t, #"Pears Share %" = _t, #"Oranges Share %" = _t, #"Leeches Share %" = _t, #"Avocado Share %" = _t, #"Kiwis Share %" = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Sales Person Name", type text}, {"Apples Sales", Int64.Type}, {"Pears Sales", Int64.Type}, {"Oranges Sales", Int64.Type}, {"Leeches Sales", Int64.Type}, {"Avocado Sales", Int64.Type}, {"Kiwis Sales", Int64.Type}, {"Total Sales", Int64.Type}, {"Apples Share %", Percentage.Type}, {"Pears Share %", Percentage.Type}, {"Oranges Share %", Percentage.Type}, {"Leeches Share %", Percentage.Type}, {"Avocado Share %", Percentage.Type}, {"Kiwis Share %", Percentage.Type}}),

    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Country", "Sales Person Name", "Apples Sales", "Pears Sales", "Oranges Sales", "Leeches Sales", "Avocado Sales", "Kiwis Sales", "Total Sales"}, "Attribute", "Value")

in

#"Unpivoted Columns"

Then add a new measure:

_Category = IF(MAXX(FILTER(ALL('Table (2)'),[Sales Person Name]=MAX('Table (2)'[Sales Person Name])),[Value])>[Gengerate Series],"Single Product","Multi Product")

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.