Getting the middle segment of Customers.

[benmcmahon]

Hello,

I have come across a problem where I am trying to segment my customer data based on another field (samples Submitted in this case), into top 20%; 40-80% and bottom 40% of customers. PowerBI only alows for getting top and bottom 'N', not the middle.

Is there a way for me to get the middle segment using DAX?

This is the final result I am looking for, but with the middle 40% as well.

All help is apprecaited.

Thanks.

[v-chenwuz-msft · Accepted solution]

Hi @benmcmahon ,

Maybe you can refer the follwoing measure to do that:

Measure = 
var _s = SUMMARIZE(ALLSELECTED('Table'),[id],"sum",SUM('Table'[values]))
var _1 = PERCENTILEX.INC(_s,[sum],0.2)
var _2 = PERCENTILEX.INC(_s,[sum],0.6)
var _20_60 = CALCULATETABLE(VALUES('Table'[id]),FILTER(_s,[sum]>=_1&&[sum]<=_2))
return
CALCULATE(COUNT('Table'[id]),FILTER('Table',[id] in _20_60))

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[benmcmahon]

Thank you very much, this appears to work well for segmenting my customer base.

I have one question though, I have a tables showing the same data that my charts do, using the same tables and report wide filters, they do not show the same number as the table when adding up the segments for each month.

I have attached my Pbix files, the 'client stats summary' and 'agent stats summary' pages are made up of the samedata from the same tables (AgentUKSampleCount & ClientUKSampleCount).

Can anyone help with why this is happening?https://techiongrouplimited-my.sharepoint.com/:u:/g/personal/ben_techiongroup_co_nz/ET6BNpl-cllMlGoC... 

[benmcmahon]

Still looking for any help with this if possible.

[amitchandak]

@benmcmahon , something similar to ABC classification

https://www.daxpatterns.com/abc-classification/

https://exceed.hr/blog/dynamic-abc-analysis-in-dax-variables/