Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count client names witin custom groups

Hi Community,

New to the Game,

 

I am in need off some help to Count client names within a custom groups i have built.

Here is the measure i have used to crrate the groups:

 

Customer Sales by Group =
CALCULATE(Reports_Policy_Transactions[TotalSales],
FILTER(VALUES(Reports_Clients_Prospects[ClientName]),
COUNTROWS(
FILTER('custom table 1',
RANKX( ALL(Reports_Clients_Prospects[ClientName]),Reports_Policy_Transactions[TotalSales],,DESC ) > 'custom table 1'[Min]
&& RANKX(ALL(Reports_Clients_Prospects[ClientName]), Reports_Policy_Transactions[TotalSales],,DESC ) <= 'custom table 1'[Max]))
> 0))
 
This gives me a total sales amount per a group and i can get % of Income per a group as well. The MeasureFTest is just a total of all clients. I would like to have the number of each group
 

 


 

.

I am needing to know how many client names i have placed within these groups.

I know how many clients i have in total, is there away to use the % income per a group and do a sum of some sort to get the count i need.

 

Can anyone please help me with a solution please.

 

 

 

 

 

Thanks John 

 

 
Need Help
  • vojtechsima's avatar
    vojtechsima
    4 years ago

    Well, a quick solution for this can be to add a new Calculated column to your Fact Table with Clients, then simply dragging everything together will be piece of cake.
    Let's say the Custom Groups are measured that if the user spent 0-5000 he is Bronze etc., giving this is the correct logic, you can write something like this:

    SWITCH(TRUE(),
[ClientSpent] >= 20001 && [ClientSpent] <= 100000, "Platinum",
[ClientSpent] >= 10001 && [ClientSpent] <= 20000, "Gold",
[ClientSpent] >= 5001 && [ClientSpent] <= 10000, "Silver",
"Bronze")

    Or please send me a sample data of your client table and we can work this out.

7 Replies

  • vojtechsima's avatar
    vojtechsima
    Super User
    4 years ago

    Hi, Anonymous 
    I would need to know how your dataset looks like, however, if you have a well-structured dataset with unique IDs, you can simply just drag the fields into Table Visual and Power BI do everything for you:
     

    But if you have unique IDs and want to use a measure, you can achieve the same result with something like this:

    CountOfIDsMeasure = 
COUNTROWS('FTE Year Rodmap')

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I think the issue is the custom table is not linked to any other table, sits by it self and only has the values that i use to measure against,

       

       so there for there is no client ID within this table, so when try and drag over ClientID it just totals them and ot against the custom groups.

       

      When try and use the Custom Sales by Group, i do not get the option to distinctCount only % or not

      • vojtechsima's avatar
        vojtechsima
        Super User
        4 years ago

        Well, a quick solution for this can be to add a new Calculated column to your Fact Table with Clients, then simply dragging everything together will be piece of cake.
        Let's say the Custom Groups are measured that if the user spent 0-5000 he is Bronze etc., giving this is the correct logic, you can write something like this:

        SWITCH(TRUE(),
[ClientSpent] >= 20001 && [ClientSpent] <= 100000, "Platinum",
[ClientSpent] >= 10001 && [ClientSpent] <= 20000, "Gold",
[ClientSpent] >= 5001 && [ClientSpent] <= 10000, "Silver",
"Bronze")

        Or please send me a sample data of your client table and we can work this out.