Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Top 20 Percent Customer

Hi I would like to solve the following question in Power BI:

>> What percentage of all my customers make up the top 20 percent of sales? 

 

For example I have the following data

 

CustomerRevenue
110
220
330
440
550
660
770
880
990
10100

Thanks in advance for your help!

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Thank you for your feedback.

     

    Please correct me if I wrongly understood.


    I guess you want to classify customers by how topN% of revenue they generate.
    In this case, what I usually do is,
    create one more table that specifies the classification.
    This table is not connected with others.
    then, I write the calculated-measure like below.

    The customers' names are written in number, and there are only 10 customers, so the visualizations might not look fancy, but please try to create a classification table and new measure like below.

     

    Rev Total by Class =
    CALCULATE (
    [Rev Total],
    FILTER (
    VALUES ( Data[Customer] ),
    COUNTROWS (
    FILTER (
    'Class by Top N % Revenue',
    [Rev Cumulate %] > 'Class by Top N % Revenue'[Min]
    && [Rev Cumulate %] <= 'Class by Top N % Revenue'[Max]
    )
    ) = 1
    )
    )
     
     

    The link to the pbix file is still the same.

     

     

    Did I answer your question? Then please mark my post as the solution.
    If I helped you, click on the Thumbs Up to give Kudos.

     

    https://drive.google.com/file/d/18W2-1nzBsBy2O2XIt-aiyoHrLqaicPld/view?usp=sharing 

     

5 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, 

    Please correct me if I wrongly understood.

     

    If it is OK with you, I want to change your question like below.

     

    >> What percentage of all my customers make up the top 40 percent of sales? 

     

    Because, in my opinion, the sample shows too small numbers of data, so I thought 40 percent might be meaningful in this case.

     

    I created the below visualizations to answer the question, and the answer is,

    Top30% of customers, ranked by revenue, generated 49.09% of all revenue.

     

    I also add a link to the PBIX file below.

     

     

    https://drive.google.com/file/d/18W2-1nzBsBy2O2XIt-aiyoHrLqaicPld/view?usp=sharing 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi,

      thank you very much for the quick feedback! That helped me a lot!

      I had another question possibly you can help me with that too? I would be very grateful!

      Namely, I also need to classify the customers by A, B, C and D. Like one does it for example with an ABC analysis.

      You are welcome to change the percentage, but e.g..
      -10 % = A customers
      - 50 % = A & B customers
      - 80 % = A & B & C customers

      Thanks in advance!!!

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        5 years ago

        Hi, Thank you for your feedback.

         

        Please correct me if I wrongly understood.


        I guess you want to classify customers by how topN% of revenue they generate.
        In this case, what I usually do is,
        create one more table that specifies the classification.
        This table is not connected with others.
        then, I write the calculated-measure like below.

        The customers' names are written in number, and there are only 10 customers, so the visualizations might not look fancy, but please try to create a classification table and new measure like below.

         

        Rev Total by Class =
        CALCULATE (
        [Rev Total],
        FILTER (
        VALUES ( Data[Customer] ),
        COUNTROWS (
        FILTER (
        'Class by Top N % Revenue',
        [Rev Cumulate %] > 'Class by Top N % Revenue'[Min]
        && [Rev Cumulate %] <= 'Class by Top N % Revenue'[Max]
        )
        ) = 1
        )
        )
         
         

        The link to the pbix file is still the same.

         

         

        Did I answer your question? Then please mark my post as the solution.
        If I helped you, click on the Thumbs Up to give Kudos.

         

        https://drive.google.com/file/d/18W2-1nzBsBy2O2XIt-aiyoHrLqaicPld/view?usp=sharing