Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
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...
Still looking for any help with this if possible.
@benmcmahon , something similar to ABC classification
https://www.daxpatterns.com/abc-classification/
https://exceed.hr/blog/dynamic-abc-analysis-in-dax-variables/
User | Count |
---|---|
99 | |
86 | |
78 | |
75 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |