How to roll up customer stage to a higher level (group level) based on criteria?

[Brij]

Hi,

I have a table that has group and customer data along with its stages (at customer level). A group has more than one customers and each customer are at different stages. I would like to see the stage at a group level based on a criteria as per below.

If a group has three customers and each customer is in a different stages - i.e. Stage 1, Stage 2, Stage 3 then I would like this group should be sitting at stage 1

If a group has two customers and each customer is in a different stages - i.e. Stage 2, Stage 3 then I would like this group should be sitting at stage 2

If a group has one customer and is at stage 1 then I would like this group should be sitting at stage 1

If a group has one customer and is at stage 2 then I would like this group should be sitting at stage 2

Here is my table. My desire result is in column D

GrpID	Customer	Stage	Desire result
G001	cust1	stage1	group with stage 1
	cust2	stage2
	cust3	stage3
G002	cust4	stage2	group with stage 2
	cust5	stage3
G003	cust6	stage1	group with stage 1
G004	cust7	stage2	group with stage 2

Any help is appreciated.

Thank you,

Pet

[Brij]

Hi @grazitti_sapna ,

This is great. Exactly what I was looking for.

I know I had given a specific sample and your solution is perectly catering for that. However, I just want to see if is there anyway we can twick this a bit when business wants to change the rule and say if a group has three customers with each stages but they want to mark the group as "Stage 2" or "Stage 3" depending on what the business decides. 

If we can have something flexible that we can change quickly based on the business rule would be great.

Thank you,

Pet

[Anonymous]

Your solution is so great grazitti_sapna 

Hi, @Brij 

I wish you all the best. Previously Super user have provided a solution to help you solve the problem. Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

Best Regards

Jianpeng Li

[grazitti_sapna · Accepted solution]

Hi @Brij,

Glad you found the solution useful! Since business requirements can change, here’s a more flexible approach to rolling up customer stages at the group level. This way, if the business decides that a group with three customers should be marked as "Stage 2" instead of "Stage 1," you won’t have to rewrite the logic every time.

Solution 1: Use a Parameter Table for Business-Defined Rules
Instead of hardcoding the logic, create a parameter table (e.g., Stage Rules) where business users can set the preferred rule. Example:

Min Customers	Group Stage Assignment
3 Customers	Stage 2
2 Customers	Stage 3

Then, modify your DAX to dynamically look up the stage assignment:

Group Stage Dynamic = 
VAR CustomersInGroup = CALCULATE(
DISTINCTCOUNT('Table'[Customer]),
ALLEXCEPT('Table', 'Table'[GrpID])
)
VAR MinStage = CALCULATE(
MIN('Table'[Stage]),
ALLEXCEPT('Table', 'Table'[GrpID])
)
VAR CustomStage = LOOKUPVALUE(
'Stage Rules'[Group Stage Assignment],
'Stage Rules'[Min Customers], CustomersInGroup
)
RETURN
IF(
ISBLANK(CustomStage),
"Group with " & MinStage, -- Default rule (min stage)
"Group with " & CustomStage -- Business-defined rule
)

Why This Works:

• Business users can update the rule without modifying DAX
• If no rule is set, it defaults to the minimum stage
• Scales easily as rules evolve

Solution 2: Use a What-If Parameter for Dynamic Selection
If the business wants on-the-fly control, a What-If parameter lets users choose between different logic (e.g., Min Stage, Max Stage, Most Frequent Stage).

Create a What-If Parameter called Stage Selection with values:

• Min Stage
• Max Stage
• Most Frequent Stage

Modify your DAX measure:
Group Stage Dynamic = 
VAR SelectedRule = SELECTEDVALUE('Stage Selection'[Value], "Min Stage")
VAR MinStage = CALCULATE(MIN('Table'[Stage]), ALLEXCEPT('Table', 'Table'[GrpID]))
VAR MaxStage = CALCULATE(MAX('Table'[Stage]), ALLEXCEPT('Table', 'Table'[GrpID]))
VAR MostFrequentStage = CALCULATE(
TOPN(1, SUMMARIZE('Table', 'Table'[Stage], "Count", COUNT('Table'[Customer])), [Count], DESC),
ALLEXCEPT('Table', 'Table'[GrpID])
)

RETURN 
SWITCH(
SelectedRule,
"Min Stage", "Group with " & MinStage,
"Max Stage", "Group with " & MaxStage,
"Most Frequent", "Group with " & MostFrequentStage,
"Group with " & MinStage -- Default to Min Stage
)

Why This Works:

• Users select the logic from a slicer instead of modifying the report
• Supports multiple business scenarios without extra work
• More flexible for future changes

Which One to Use?

✅ If the business has fixed rules, go with Solution 1 (Parameter Table)
✅ If they need dynamic control, use Solution 2 (What-If Parameter)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[Brij]

Thank you @grazitti_sapna ,

[grazitti_sapna]

Hi @Brij ,
Since you want to assign a group stage based on the lowest stage within each group, here are two approaches:

1. Using a Calculated Column (For Static Calculation)
If you want a column that always reflects the lowest stage per group, try this:

Group Stage = 
VAR MinStage = CALCULATE(
MIN('Table'[Stage]),
ALLEXCEPT('Table', 'Table'[GrpID])
)
RETURN
"Group with " & MinStage

This ensures each group is assigned the lowest stage among its customers.
2. Using a Measure (For Dynamic Calculation in Visuals)
If you need a measure that updates dynamically in your visuals, use:

Group Stage Measure = 
VAR MinStage = MINX(
VALUES('Table'[Stage]),
'Table'[Stage]
)
RETURN
"Group with " & MinStage

Expected Outcome
With this approach, your group-level stage will be:

• If a group has Stage 1, 2, and 3, it will be assigned Stage 1
• If a group has Stage 2 and 3, it will be assigned Stage 2
• If a group has only one stage, it will retain that stage

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!