Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |