cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Create Category based on product % share

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.

1 ACCEPTED SOLUTION
Community Support

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:

1. 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"``````

``_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.

3 REPLIES 3
Community Support

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:

1. 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"``````

``_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.

Helper II

Hi

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

Helper IV

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.