I want to create calculated measures bin that categorize data into specific percentage ranges

[Hemant_Jaiswar]

I want to create calculated measures that categorize data into specific percentage ranges (e.g., 0-30%, 30-70%, 70-100%), and then use these categories as column headers.

this is my demo data 

JOB_NAME	JOB_STATUS	STATs	APPOINTMENT_DATE
TV Installation Service	NOT_SERVICED	CANCELLED	01-10-2024
TV Installation Service	NOT_SERVICED	COMPLETED	01-10-2024
TV Installation Service	NOT_SERVICED	TV Installation Service	01-10-2024
TV Installation Service	NOT_SERVICED	TV Installation Service	01-10-2024
TV Installation Service	NOT_SERVICED	FULFILLMENT_HOLD	01-10-2024
TV Installation Service	NOT_SERVICED	COMPLETED	01-10-2024
TV Installation Service	NOT_SERVICED	FULFILLMENT_HOLD	01-10-2024
TV Installation Service	NOT_SERVICED	FULFILLMENT_HOLD	01-10-2024
TV Installation Service	NOT_SERVICED	FULFILLMENT_HOLD	01-10-2024
TV Installation Service	NOT_SERVICED	FULFILLMENT_HOLD	01-10-2024

i have used this to calculate 

Service Completion Rate =

DIVIDE(

    COUNTROWS(FILTER('Sheet1', 'Sheet1'[STATs] = "COMPLETED")),

    COUNTROWS('Sheet1'),

    0)

and then for bins this 

Dynamic Header =

SWITCH(

    TRUE(),

      Sheet1[% Completed] <= 0.30, "0-30%",

   Sheet1[% Completed] <= 0.70, "30-70%",

    Sheet1[% Completed] <= 0.1, "70-100%",

    "Out of Range")

now i am getting only one range 30-70 %, which falls under total completion rate which is 37.27 what about other ranges 

I WANT RESULT SOMETHING LIKE THIS 

	completion rate %
count of job	0-30%	30-60%	60-100%
0-300	80(Count of technician)	72	60
300-1000	57	82	84
1000-2000	52	61	95
2000-3500	98	53	95

[Hemant_Jaiswar · Accepted solution]

Hello,

Thank you, @Bmejia and @Anonymous, for your solutions. However, I identified the root cause of my issue. I had underestimated the importance of contextual evaluation. Once I based my Completion Rate calculation on the relevant dimension, I was able to successfully derive all the necessary ranges.

[Hemant_Jaiswar · Accepted solution]

Hello,

Thank you, @Bmejia and @Anonymous, for your solutions. However, I identified the root cause of my issue. I had underestimated the importance of contextual evaluation. Once I based my Completion Rate calculation on the relevant dimension, I was able to successfully derive all the necessary ranges.

[Anonymous · Accepted solution]

Hi @Hemant_Jaiswar ,
Glad to hear you've found a solution! If you're sure the issue has been resolved, could you mark this post as resolved? That way, others with similar issues can more easily find a solution and the community can see that the issue has been resolved.
Thanks, and feel free to reach out if you need further help!

[Anonymous]

Hi All,
Firstly Bmejia thank you for your solution!
And @Hemant_Jaiswar ,According to your description, you want to show three different cases, even the null value should be shown, right?
If we use the Calculated column, we will see that it will only show the values that match the status, but not the other values, as shown in the figure:

To display other disappearing states, we need to create two new tables, one to describe the state you need, and one to ensure that the unique key, make sure it is a one-to-many relationship, and then you can get the effect you need.

If you still have questions, you can check my pbix file, I hope my solution can solve your problem, if I can solve your problem I will be very honored!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Bmejia]

Your Dynamic Header, should look something like this you are missing the begining part of your in between switch statement.

Dynamic Header=

SWITCH(TRUE(),

Sheet1[% Completed]<=.299,"0-30",

Sheet1[% Completed]>=.3 && Sheet1[% Completed]<=.599,"30-60",

Sheet1[% Completed]>=.6 && Sheet1[% Completed]<=1.0,"30-60",

"Out of Range")