Adding Up A Score for Values in a Column

[NiugeS]

Hi all,

I've been playing with something that is likely quite simple but having difficult working out how to do it.  I have two tables one consisting of names/items with a yes / no if they have the items.  I have a second table that has a score.  If the user has any of the item, I want it to provide a total score for each of the items.

For example using Jane:

Jane total = Car (5) + Bicycle (2) + Skateboard (0) + Garage (3) should return a total of 10 out of a total possible of 11 that I will add to a gauge.  I hope the below is clear however I have attached the pbix here for review.

If anyone has some guidance of how I create the relationships (or skip this and just add each score in a measure), please let me know.  I tried to create a measure but not sure to add up the if statements.

Many thanks

[NiugeS]

@Jihwan_Kim This is brililant!  Thank you.. didn't think to unpivot etc.  Much appreciated although I will need to spend some time understanding the measure!

[Jihwan_Kim · Accepted solution]

Score measure : =

CALCULATE (

SUMX ( Inventory, RELATED ( Score[Score] ) ),

FILTER ( Inventory, Inventory[Value] = "Yes" )

)

Score measure ALL : =

SUMX(Inventory, RELATED(Score[Score]))

Link to the sample pbix file