How to create salary Buckets for employees

[Akshaymanjunath]

I need to create buckets for employees getting salary where if it is:  >=1000 to <=5000 under "1000 to 5000"  similarly create buckets for 6000 to 10000,
11000 to 15000,
16000 to 20000,
21 to 25000,
26 to 50000,
50000  to 100000 and
100000+ for 

also salary is a calculated measure : sum(salary)
how to ensure that distinct count employee id getting the respective salary falls under the respective salary bucket

for example: x axis displays all the salary buckets and y axis supports the distinct count of hcp id

@Uzi2019 

[Anonymous]

Thanks for the reply from shafiz_p , please allow me to provide another insight:

Hi, @Akshaymanjunath 
Could you please let us know if shafiz_p's response resolved your issue? If it did, kindly accept it as the solution.

The solution provided by shafiz_p is highly effective. My approach is similar to shafiz_p's, and below is an example I created for your understanding:

1.Firstly, I created the following sample data:

2.Secondly, I used the following DAX to create a calculated column, which facilitates the filling of the X-axis:

Salary Bucket = 
SWITCH(
    TRUE(),
    [Salary] >= 1000 && [Salary] <= 6000, "1000 to 6000",
    [Salary] > 6000 && [Salary] <= 11000, "6000 to 11000",
    [Salary] > 11000 && [Salary] <= 16000, "11000 to 16000",
    [Salary] > 16000 && [Salary] <= 21000, "16000 to 21000",
    [Salary] > 21000 && [Salary] <= 26000, "21000 to 26000",
    [Salary] >= 26000 && [Salary] <= 50000, "26000 to 50000",
    [Salary] >= 50001 && [Salary] <= 100000, "50001 to 100000",
    [Salary] > 100000, "100000+",
    "Other"
)

3.Finally, I created the following measures to populate the Y-axis:

Distinct Employee Count = 
DISTINCTCOUNT('Table'[employee_id])

4.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

 
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

[shafiz_p]

Hi @Akshaymanjunath  Try create calculated column:

SalaryBucket = 
SWITCH(
    TRUE(),
    [Salary] >= 1000 && [Salary] <= 5000, "1000 to 5000",
    [Salary] >= 6000 && [Salary] <= 10000, "6000 to 10000",
    [Salary] >= 11000 && [Salary] <= 15000, "11000 to 15000",
    [Salary] >= 16000 && [Salary] <= 20000, "16000 to 20000",
    [Salary] >= 21000 && [Salary] <= 25000, "21000 to 25000",
    [Salary] >= 26000 && [Salary] <= 50000, "26000 to 50000",
    [Salary] >= 50000 && [Salary] <= 100000, "50000 to 100000",
    [Salary] > 100000, "100000+",
    "Other"
)

Create distinct employee count measure:

DistinctEmployeeCount = 
CALCULATE(
    DISTINCTCOUNT(Salary[EmployeeID]),
    ALLEXCEPT(Salary, Salary[SalaryBucket])
)

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz