Cumulative Total Percentage and Categorization by Layer

[Poison_Daddy]

Hello, 
I'm trying to categorize the cumulative percentage of different clients revenue
example
clients   | Cumulative percentage % | Layer
Client 1 | 10%                                   | Layer 50
Client 2 | 23%                                   | Layer 50
Client 4 | 42%                                   | Layer 50
Client 5 | 55%                                   | Layer 50
Client 6 | 61%                                   | Layer 80
.

.

.

.
Client X | 100%                                   |

My issue is that if I where to make a condition to throw Layer 50 to those =< than 50% then it won't take in account those Clients that make it reach 50% 
like in the example
Client 4 was at 42%
and client 5 made it reach 50% but went overboard by 5% but still is considered Layer 50% because it was the one who made it reach that value

How can I make this work?

TotCA= is basically Sum(Sales)

[DallasBaba]

@Poison_Daddy I look forward to hearing from you once you incorporate the solution in your work.

If this post is helpful, please mark it as the solution to help others find it easily. Also, if my answers contribute to a solution, show your appreciation by giving it a thumbs-up

[DallasBaba]

@Poison_Daddy  you can categorize the cumulative percentage of different clients’ revenue in a table by following the approach below:

1. Sort the clients in descending order of their cumulative percentage.
2. Assign the first client to Layer 50.
3. For each subsequent client, check if its cumulative percentage is less than or equal to 50%. If so, assign it to Layer 50. If not, assign it to the next available layer (in this case, Layer 80).
4. Repeat step 3 for all remaining clients.

Using this approach, you can ensure that the clients are categorized correctly based on their cumulative percentage and that the layer assignments are made in a way that reflects the contribution of each client to the overall percentage.

You can achieve this using a calculated column to determine the layer based on cumulative percentages.

Here's a DAX solution:

Layer = 
VAR CumulativePercentage = 
    CALCULATE(
        SUM(SalesTable[Percentage]),
        FILTER(
            SalesTable,
            SalesTable[ClientName] = EARLIER(SalesTable[ClientName]) &&
            SalesTable[ProductBrand] = EARLIER(SalesTable[ProductBrand]) &&
            SalesTable[Product] = EARLIER(SalesTable[Product])
        )
    )
RETURN
    IF(CumulativePercentage <= 50%, "Layer 50", "Layer 80")

This DAX formula uses the variable CumulativePercentage to calculate the cumulative percentage for each row based on the current client, product brand, and product. Then, it checks if the cumulative percentage is less than or equal to 50% and assigns the layer accordingly.

Add this calculated column to your table, and you can dynamically determine the layer based on cumulative percentages. Make sure to adjust column and table names as per your actual data structure.

Please note that DAX calculations are sensitive to the relationships between tables in Power BI, so ensure that your data model is appropriately structured for accurate results.

Let me know if this works for you. @ me in replies, or I'll lose your thread!!!  
Note:

If this post is helpful, please mark it as the solution to help others find it easily. Also, if my answers contribute to a solution, show your appreciation by giving it a thumbs up! 

[Poison_Daddy]

Hello @DallasBaba Thanks for the message 
and sorry for the late reply,
I will check this function EARLIER it seems to be the answer to these issue and will incorporate the measure into my PWBI

I will let you know if it ends up working

[DallasBaba]

@Poison_Daddy To create a Pareto chart, you can follow these steps:

1. Sort the clients in descending order based on their revenue.
2. Calculate the cumulative percentage of revenue for each client.
3. Plot the cumulative percentage of revenue on the y-axis and the clients on the x-axis.
4. Draw a line chart representing the cumulative percentage of revenue.

Using this chart, you can easily identify the clients that contribute the most to the total revenue. For example, if you want to categorize the cumulative percentage of revenue into two layers, you can draw a horizontal line at 50% on the y-axis.

Let me know if this works for you. @ me in replies, or I'll lose your thread!!!  
Note:

If this post is helpful, please mark it as the solution to help others find it easily. Also, if my answers contribute to a solution, show your appreciation by giving it a thumbs up! 

[Poison_Daddy]

@DallasBaba Hello, that works if I want to show as a visual and its a great way to display it, thank you for that didn´t know about Pareto Chart.
But the issue is still the same I want to identify on a Table because there are gonna be cases where I will have to make it by couples
Example
.
.
.

Client name | Brand of the product | product | 22% | Layer 50
Client name | Brand of the product | product | 38% | Layer 50
Client name | Brand of the product | product | 52% | Layer 50
Client name | Brand of the product | product | 61% | Layer 80
If I where to highlight by making a rule on the Columns to show a different Color whenever is from 0-50% then it will discard (in this Example 52%), I want it to consider it because it's the one that made it reach that 50% mark