Distinct Count by Sum of Column

[sarhutson]

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? 

[AnthonyJoseph · Accepted solution]

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 · Accepted solution]

@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.

[AnthonyJoseph · Accepted solution]

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]

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"?