Percentage segmentation ok %column

[Anonymous]

Hi,

I have query with % in column and I would like to creage segmentation like upper 20% mid 20-80% low 80-20%

this is example of query  where I would like to add another column with the segmentation

to this 

I guess I could use DAX but I am not sure how in this case.

thank you a lot.

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. Add Column – Index Column – From 1.

2. Create calculated column.

%per =
CALCULATE(SUM('Table'[% column]),FILTER(ALL('Table'),'Table'[Index]<=EARLIER('Table'[Index])))

Segementation =
var _highestvalue=100
var _20per=_highestvalue * 0.002
var _20per80per=_highestvalue * 0.008
return
SWITCH(TRUE(),
'Table'[%per] <=_20per,"UPPER 20%",
'Table'[%per] >_20per&&'Table'[%per]<=_20per80per,"MIDDLE 80-20%",
"low 80-20%")

3. Result:

Best Regards,

Liu Yang

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

[Anonymous]

omg you are best, thank you a lot. It works like charm

[v-yangliu-msft]

Hi  @Anonymous ,

Not very clear what you mean.... can you express your expected results in the form of pictures, we can help you better.

Best Regards,

Liu Yang

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

[Anonymous]

Hi Liu, 

below is the picture I hope this will help to understand me more? 

[v-yangliu-msft]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated column.

Segementation =
SWITCH(
    TRUE(),
    'Table'[% column] <=0.01,"Mid 20-80%",
    'Table'[% column] >0.01&&'Table'[% column]<=0.2,"upper 20%",
    "low 80-20%"
)

2. Result:

Best Regards,

Liu Yang

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

[Anonymous]

Hi Liu,

thanks a lost. The problem in my  case is that I need to sum up the % to get the upper 20% so lets say I have 20 lines with 1% result and that would be the upper 20% and then the rest would be 20-80% but again as sum. let me know if it make sense? 

[VahidDM]

Hi @Anonymous 

How do you want to assign segmentation code to the data? any calculated?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/