Using DAX Only to rank and group Values into groups of 3

[Anonymous]

Good evening community!

I have a new problem today.  I am trying to get a list of Clients to break into groups.   In natural language, "these are our Top 1/3 clients based on Sales, these are our middle and bottom 1/3's based on Sales"   The trick here, is that it won't be just sales.  It'll be # of Projects, estimates values, hours entered, a whole number of things.   In a perfect world, Id like to be able to build a dax expression that I could use as a filter to say "Show me Top 1/3" and my report would filter all based on the above.   I say this needs to be done in DAX because i have Mutlipe data sources joined together, and do not have a single table i could use to ad a calculated column to.

To visualize, please see below.   This is my sample data, shown as a table after sums are done.

This would be what i would get to with a measure.

Which would allow me to get to something along the lines below.  where I could have 1 slicer or filter, and changing that would change multiple visuals on my report.

         

I'm not sure what im asking is possible, but thought this was the best place to start.   If a clearer description is required, I'll be happy to do my best. 

[v-yuaj-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, you can create calculated columns as follows.

sales_index =

var x1 = RANKX('CASE1',CASE1[Sales],,DESC)

var x2=DISTINCTCOUNT('CASE1'[Client])

return

IF(

  x1<=x2/3,

  "1",

  IF(

      x1>2*x2/3,

      "3",

      "2"

    ))

projects_index =

var x1 = RANKX('CASE1',CASE1[Projects],,DESC)

var x2=DISTINCTCOUNT('CASE1'[Client])

return

IF(

  x1<=x2/3,

  "1",

  IF(

      x1>2*x2/3,

      "3",

      "2"

    ))

Result:

Best Regards,

Yuna

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

[amitchandak]

@Anonymous , You need have percentile and segmentation to work together

https://blog.enterprisedna.co/implementing-80-20-logic-in-your-power-bi-analysis/
https://forum.enterprisedna.co/t/testing-the-pareto-principle-80-20-rule-in-power-bi-w-dax/459
https://finance-bi.com/power-bi-pareto-analysis/

segmentation

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