Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 Name | Transaction Volume | Cumulative Transaction | Cumulative % | Name |
Name 1 | 92 | 92 | 15.67% | Name 1 |
Name 2 | 84 | 176 | 29.98% | Name 2 |
Name 3 | 73 | 249 | 42.42% | Name 3 |
Name 4 | 72 | 321 | 54.68% | Name 4 |
Name 5 | 53 | 374 | 63.71% | Name 5 |
Name 6 | 48 | 422 | 71.89% | Name 6 |
Name 7 | 47 | 469 | 79.90% | Name 7 |
Name 8 | 35 | 504 | 85.86% | Other |
Name 9 | 33 | 537 | 91.48% | Other |
Name 10 | 31 | 568 | 96.76% | Other |
Name 11 | 5 | 573 | 97.61% | Other |
Name 12 | 4 | 577 | 98.30% | Other |
Name 13 | 2 | 579 | 98.64% | Other |
Name 14 | 1 | 587 | 100.00% | Other |
Solved! Go to Solution.
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.
Regards,
Cherie
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...)
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" )
Regards,
Cherie
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" )
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")
Regards,
Cherie
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
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.
Regards,
Cherie
@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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |