cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Pareto (80/20) Segmentation

Hi,
I created 80/20 segmentation in my data model and I got what I wanted (see the table below).
Now I want to calculate new Name column with the next logic: if Cumulative % <=80% show value from the "Customer Name" column, otherwise show "Other" (the result will be the column Name as in the table below).

 

I tried with this calculated column but it doesn't work (the result isn't correct, it's always "Other"):

80/20 Name = IF([Cumulative Percen.] <= 0.8, SalesReport[Names], "Other")

How can I do this?

 

----------
In the next step, I'll use a pie chart to show this segmentation where all customers with small cumulative transactions will be categorized as Other.

 

Customer NameTransaction VolumeCumulative TransactionCumulative %Name
Name 1929215.67%Name 1
Name 28417629.98%Name 2
Name 37324942.42%Name 3
Name 47232154.68%Name 4
Name 55337463.71%Name 5
Name 64842271.89%Name 6
Name 74746979.90%Name 7
Name 83550485.86%Other
Name 93353791.48%Other
Name 103156896.76%Other
Name 11557397.61%Other
Name 12457798.30%Other
Name 13257998.64%Other
Name 141587100.00%Other
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Pareto (80/20) Segmentation

Hi @lazzarjovvch74

 

You may use 'add index column' function in Query Editor to get the index column. Then the calculated columns can be used in your data.

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
Highlighted
Solution Sage
Solution Sage

Re: Pareto (80/20) Segmentation

Hi @lazzarjovvch74

 

where is the Cumulative Percen. column coming from? is it a measure?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Highlighted
Helper I
Helper I

Re: Pareto (80/20) Segmentation

Hi @LivioLanzo

Yes, it is a calculated measure (this is a solution for Cumulative percentage: https://community.powerbi.com/t5/Desktop/Top-customers-who-are-contributing-to-the-80-of-the-total-a...)

Highlighted
Solution Sage
Solution Sage

Re: Pareto (80/20) Segmentation

@lazzarjovvch74 are you able to share your file ?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Highlighted
Microsoft
Microsoft

Re: Pareto (80/20) Segmentation

Hi @lazzarjovvch74

 

You may try to create a measure to get the name as requested. For example:

80/20 Name =
IF ( [Cumulative Percentage] <= 0.8, MAX ( Table[Customer Name] ), "other" )

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Pareto (80/20) Segmentation

Hi @v-cherch-msft

Yes, your solution works and gives the correct result in this tabular format. But it is a measure, so I can't use it as a dimension or category to slice data on pie chart for an example. I tried to create a calculated column using the same code but it always gives "Other" as a result.

80/20 Name =
IF ( [Cumulative Percentage] <= 0.8, MAX ( Table[Customer Name] ), "Other" )
Highlighted
Microsoft
Microsoft

Re: Pareto (80/20) Segmentation

Hi @lazzarjovvch74

 

It seems you may try to add calculated columns to get it as requested. For example:

Cumulative Transaction = SUMX(FILTER(Table2,Table2[Index]<=EARLIER(Table2[Index])),Table2[Transaction Volume])
Cumulative % = Table2[Cumulative Transaction]/SUM(Table2[Transaction Volume])
80/20Name = IF(Table2[Cumulative %]<=0.8,Table2[Customer Name],"other")

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Pareto (80/20) Segmentation

Hi @v-cherch-msft

 

I believe that it works on your side, but I see circular dependency error in my file. I created a simple file where I tried to do this and you can see it on this link: https://drive.google.com/file/d/1ZHuDGXl5kuT3TfD3oYtaFixT073uUDud/view?usp=sharing

Highlighted
Microsoft
Microsoft

Re: Pareto (80/20) Segmentation

Hi @lazzarjovvch74

 

You may use 'add index column' function in Query Editor to get the index column. Then the calculated columns can be used in your data.

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper I
Helper I

Re: Pareto (80/20) Segmentation

@v-cherch-msftThank you, this works like a charm! Best

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors