Forum Discussion

sofias's avatar
sofias
Frequent Visitor
4 years ago

Help with distinct calculate on 2 columns

I have a table with 2 columns. "Sales Rep" and "Battle Name" as following sample:

 

Sales RepBattle Name
[email protected]LESS_L2W_3_ORDERS
[email protected]NOT_ACTIVE_L13W
[email protected]NOT_ACTIVE_L13W
[email protected]LESS_L2W_3_ORDERS
[email protected]LESS_L2W_3_ORDERS
[email protected]NOT_ACTIVE_L13W
[email protected]NOT_ACTIVE_L13W
[email protected]LESS_L2W_3_ORDERS
[email protected]NOT_ACTIVE_L13W
[email protected]LESS_L2W_3_ORDERS
[email protected]NOT_ACTIVE_L13W
[email protected]LESS_L2W_3_ORDERS
[email protected]LESS_L2W_3_ORDERS
[email protected]NOT_ACTIVE_L13W
[email protected]LOWER_L4W_GMV
[email protected]LESS_L2W_3_ORDERS
[email protected]NOT_ACTIVE_L13W
[email protected]LESS_L2W_3_ORDERS
[email protected]LESS_L2W_3_ORDERS
  

 

I want to calculate how many sales rep has 1 battle, how many have 2 and so on. I tryied a lot of things such as distinctcount, filter, group by but none of them made me able to achieve the result below:

 

Number of BattlesTotal of Sales Reps
12
28
31
40
50
60

 

Can anyone help me, explaining, how can I get the result I need?

Need Help

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    1. create a disconnected table

     

     

    Battles = GENERATESERIES(1,6)

     

     

    2.  for each value of this table, compute the number of reps that fall into that bucket. That can be done as a measure or a calculated column - the example here is for a calculated column.

     

     

    #SR = 0+countrows(filter(summarize('Table','Table'[Sales Rep],"Battles",count('Table'[Battle Name])),[Battles]=[Value]))

     

     

    Lastly, display the result in a table.  Do not summarize [Value], but show items with no data.