Forum Discussion

dbrandone's avatar
dbrandone
Helper IV
5 years ago
Solved

Ranking Customers

Hi Everyone,

 

I am still new to Power BI, but today I feel as if I have learned alot and started putting good measures together. I am currently trying to put together a measure that will show the Top 10 customers that we have with the amount of items that they have purchased. I would also like to expand this later by taking that further and showing each customer and the individual products they purchased. We only have 6-8 items that we sell, but really 99% of the items that we sell are one of 3 items. Each customer has a row created each time they purchase one item. One row means one item. I was trying to perform an initial countrows measure and have that show up per customer. Then do a rankx measure to pull the top 10 desc. When I pull the final measure onto a chart to see the data before expanding, many times I get a chart with each customer on the list and the number 1 as the total for each customer. 

 

Any help would be appreciated. Here is what I have so far.

 

I first calculated the rows of the column with the customers name. I had to get rid of blank customer rows first as they can be scattered throughout. (We deal with very sensitive data so I have made the column names generic).

 

                  NoBlankTotalCustomers = CALCULATE(COUNT(Table1_Sales[CustomerName]),
                                                                            Filter(Table1, [CustomerName]<>"Null"))
 
 I was then working on the Rankx measure and this is where I have gotten nowhere. 
 
Lastly, I was trying to take the Rankx measure and input it into this last measure to order the Top 10. Here is that last measure.
 
               CustomerTop10 = CALCULATE(Table1_Sales[NoBlankTotalCustomers],
                                                                   FILTER(Table1_Sales,
                                                                                 Table1_Sales[###Where my rankx measure will go###] <=10))
 
Do I have the formatting right to get the top 10 or have I made the flow of getting to this point too difficult?
 
 
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    Try this measure

    =calculate([NoBlankTotalCustomers],top(10,all(Table1_Sales[CustomerName]),[NoBlankTotalCustomers]),VALUES(Table1_Sales[CustomerName]))

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    Try this measure

    =calculate([NoBlankTotalCustomers],top(10,all(Table1_Sales[CustomerName]),[NoBlankTotalCustomers]),VALUES(Table1_Sales[CustomerName]))

    • dbrandone's avatar
      dbrandone
      Helper IV
      5 years ago

      Thanks for sending me that measure Ashish_Mathur. I received an error on the "Top" filter. I instead was able to figure out a way to take the "NoBlankTotalCustomers" measure with the Customer Name column in a table and then filter the visual using Top N and then sort DESC by TotalCustomers.

       

      Now I just need to figure out a way to then take the list that I have now and take the total customers column that has a number attached with it and then branch off and show the amount of each item they purchased to get to that number.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        Hi,

        My proposed measure is correct.  I cannot what istake you are committing.