The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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/
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
94 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |