Create Category based on product % share

[Anno2019]

Hi Guru's

Need help on this.

I am trying to create a Dax formula that will allow us to see if a salesperson is selling more of one product.

I tried to explain with the below.

Below column Q5 to Q7 is my example.  If the salesperson has sold a product that is greater 80% of the total share of products sold then it shows as "Single Product", if not, then "Multi Product".

I have a generated series field called [generated serious] which I will you in a slicer, allowing the % to be changed if need be.  The example below is if the series was above 80%.

Any guidance would be appreciated.

 

[v-jianboli-msft · Accepted solution]

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.

[indkitty]

Hi 

do you have this data in excel if yes, can you share it?

[Anno2019]

Hi indkitty

Would this help?

Country	Sales Person Name	Apples Sales	Pears Sales	Oranges Sales	Leeches Sales	Avocado Sales	Kiwis Sales	Total Sales	Apples Share %	Pears Share %	Oranges Share %	Leeches Share %	Avocado Share %	Kiwis Share %	Category
Germany	Peter Parker	1500	20	2000	9000	50	55000	67570	2%	0%	3%	13%	0%	81%	Single Product
China	Micky Mouse	1611	131	2111	9111	161	5000	18125	9%	1%	12%	50%	1%	28%	Multi Product
Estonia	Black Panther	834	56000	1334	8334	750	2000	69252	1%	81%	2%	12%	1%	3%	Single Product