Forum Discussion

sarhutson's avatar
sarhutson
Frequent Visitor
3 years ago
Solved

Distinct Count by Sum of Column

Hello! I'm looking to count distinct sales rep by summarized commission values. I have page slicers such as date and plan so I'm trying to keep this dynamic. Anytime I try to get a count of sales rep by commission it rolls into one large sum or breaks out by customerID instead of Sales Rep. 

Example Table: 


This is what I keep getting in PowerBI: 

 

This is my desired result: 



The end goal will be to get a count of sales rep in buckets of "Less than 300","Less than 500","Less than 700", etc. this is a simplified example 

 

Any suggestions? 

Need Help
  • AnthonyJoseph's avatar
    AnthonyJoseph
    3 years ago

    Hi sarhutson 
    In my view, to achieve this we need to create a calculated column (grouped by name) and a measure.

    I have mocked up the table name to be "Commission data" and sales rep to be "Name" and below are the codes.

     

    1) Calculated column

     

    grouped by name = 
var _name = 'Commission data'[Name]
var _value =  SUMX(FILTER('Commission data','Commission data'[Name] = _name),'Commission data'[Commission])
return 
SWITCH(TRUE(),
_value <=300, "Less than or equal to 300",
_value <=500, "Less than or equal to 500",
_value <=700,"Less than or equal to 700")

     

     

    2)Measure will be to get the uniqe sales reps: 

     

    unique sales reps = DISTINCTCOUNT('Commission data'[Name])

     

     

    Then, I added these two in a table to get the desired output:

     

     

    Hope this helps!

     

    Thanks

    AnthonyJoseph

  • parry2k's avatar
    parry2k
    3 years ago

    sarhutson you are looking for dynamic segmentation, check this blog post  Dynamic segmentation – DAX Patterns

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

     

3 Replies

  • AnthonyJoseph's avatar
    AnthonyJoseph
    Resolver III
    3 years ago

    Hi sarhutson 
    In my view, to achieve this we need to create a calculated column (grouped by name) and a measure.

    I have mocked up the table name to be "Commission data" and sales rep to be "Name" and below are the codes.

     

    1) Calculated column

     

    grouped by name = 
var _name = 'Commission data'[Name]
var _value =  SUMX(FILTER('Commission data','Commission data'[Name] = _name),'Commission data'[Commission])
return 
SWITCH(TRUE(),
_value <=300, "Less than or equal to 300",
_value <=500, "Less than or equal to 500",
_value <=700,"Less than or equal to 700")

     

     

    2)Measure will be to get the uniqe sales reps: 

     

    unique sales reps = DISTINCTCOUNT('Commission data'[Name])

     

     

    Then, I added these two in a table to get the desired output:

     

     

    Hope this helps!

     

    Thanks

    AnthonyJoseph

    • sarhutson's avatar
      sarhutson
      Frequent Visitor
      3 years ago

      Thank you AnthonyJoseph ! This is very helpful!! Is there anyway to calculate the groupings in a measure so they will flex off of the date and plan columns so its dynamic? For example if I select the remote plan all 5 associate would then fall under "Less than or equal to $300"?

  • parry2k's avatar
    parry2k
    Super User
    3 years ago

    sarhutson you are looking for dynamic segmentation, check this blog post  Dynamic segmentation – DAX Patterns

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.