Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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

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
LivioLanzo
Solution Sage
Solution Sage

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!  

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...)

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.

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" )

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.

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

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.

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

@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!  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.