cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rocky09 Established Member
Established Member

Assign Value based on other Values in the Group

Here is the situation, i have below table. You see, I have groups and it has different scores.  I need to find a way to assign a value i.e. ">50" or "<50" based on values in the group.

 

Example:

 

Group A contains Scores like, 42,49,55 - in this, i need to look for any value which is greater than 50, if it is there, the entire group will be assiged a value as ">50"

 

TicketGroupScore
12453A42
12477A49
12501A55
12525B45
12549B60
12573B54
12597C62
12621C12
12645C45
12669C45
12693D80
12717E45
12741E45
12765E82
12789F45
12813F24

 

 

Result Expecting:

 

TicketGroupScoreResult
12453A42>50
12477A49>50
12501A55>50
12525B45>50
12549B60>50
12573B54>50
12597C62>50
12621C12>50
12645C45>50
12669C45>50
12693D80>50
12717E45<50
12741E45<50
12765E82>50
12789F45<50
12813F24<50

 

anyway to do this in PowerBI?

 

Thank you in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Assign Value based on other Values in the Group

Hey,

 

given your sample data, I do not understand your expected result for group E

 

There is a value 82 for ticket 12765, for this reason I would expect that the whole group E is marked with >50.

 

I guess this DAX statement can be used to create a calculated column:

bin Score = 
    CALCULATE(
        IF(MAX('Table1'[Score])>50,">50","<50")
        ,ALLEXCEPT('Table1',Table1[Group])
    ) 

This creates the following

Value by Group.png

 

Hope this helps

 

Regards

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
2 REPLIES 2
Super User
Super User

Re: Assign Value based on other Values in the Group

Hey,

 

given your sample data, I do not understand your expected result for group E

 

There is a value 82 for ticket 12765, for this reason I would expect that the whole group E is marked with >50.

 

I guess this DAX statement can be used to create a calculated column:

bin Score = 
    CALCULATE(
        IF(MAX('Table1'[Score])>50,">50","<50")
        ,ALLEXCEPT('Table1',Table1[Group])
    ) 

This creates the following

Value by Group.png

 

Hope this helps

 

Regards

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
rocky09 Established Member
Established Member

Re: Assign Value based on other Values in the Group

Perfect.

 

Thank you so much.