Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Percentage segmentation ok %column

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.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

     

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Liu, 

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