Percentage Grouping Power BI

[Mikester_104]

New to Power BI - need help.

I have a very basic calculation that percentage between -100% and 1,000% - I am trying to group these so into a model as i have 13,000 records each with a different percetange.

I have already taken my on hand QTY and Set Safety Stock Value and completed the % over or under Safety Stock value. Whcih is hte %.

Want to Bucket or Group these into a range:

Group	Low	High
Safety Stock Gone	-100%	-100%	anythin that falls between these two %'s add gone to new column
75% of Safety Stock Gone	-75%	-99%
50% of Safety Stock Gone	-50%	-74%
25% of  Safety Stock Gone	-25%	-49%
Just Entered into SS	0%	-24%
25% Above Safety Stock	1%	25%
50% Above Safety Stock	26%	50%
75% Above Safety Stock	51%	75%
100% Above Safety Stock	76%	1000000%

[pranit828]

Hi @Mikester_104 

Can you post the sample input and output for these records.

Regards,

Pranit

[Mikester_104]

Sku	Description	On Hand	Safety Stock	% into SS
1234	Tooth Brush	100	200	-50.00%
1111	Dental Floss	500	200	150.00%
4321	Toothpaste	50	500	-90.00%

[v-yingjl · Accepted solution]

Hi @Mikester_104 ,

If I got it correctly, you can create this calculated column using switch() statement:

Group = 
SWITCH (
    TRUE (),
    'Table'[% into SS] = -1, "Safety Stock Gone",
    'Table'[% into SS] >= -0.99
        && 'Table'[% into SS] <= -0.75, "75% of Safety Stock Gone",
    'Table'[% into SS] >= -0.74
        && 'Table'[% into SS] <= -0.5, "50% of Safety Stock Gone",
    'Table'[% into SS] >= -0.49
        && 'Table'[% into SS] <= -0.25, "25% of Safety Stock Gone",
    'Table'[% into SS] >= -0.24
        && 'Table'[% into SS] <= 0, "Just Entered into SS",
    'Table'[% into SS] >= 0.1
        && 'Table'[% into SS] <= 0.25, "25% Above Safety Stock",
    'Table'[% into SS] >= 0.26
        && 'Table'[% into SS] <= 0.5, "50% Above Safety Stock",
    'Table'[% into SS] >= 0.51
        && 'Table'[% into SS] <= 0.75, "75% Above Safety Stock",
    'Table'[% into SS] >= 0.76
        && 'Table'[% into SS] <= 10000, "100% Above Safety Stock",
    "No corresponded data"
)

Best Regards,
Yingjie Li

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

[Ashish_Mathur]

Hi,

Are you looking for a measure or a calculated column solution?

[amitchandak]

@Mikester_104 , not very clear. see if dynamic segmentation

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization