Forum Discussion

NiugeS's avatar
NiugeS
Helper V
5 years ago
Solved

Adding Up A Score for Values in a Column

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

 

  •  

    Score measure : =
    CALCULATE (
    SUMX ( Inventory, RELATED ( Score[Score] ) ),
    FILTER ( Inventory, Inventory[Value] = "Yes" )
    )
     
     
    Score measure ALL : =
    SUMX(Inventory, RELATED(Score[Score]))
     
     
     
     

2 Replies

  • 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!

  •  

    Score measure : =
    CALCULATE (
    SUMX ( Inventory, RELATED ( Score[Score] ) ),
    FILTER ( Inventory, Inventory[Value] = "Yes" )
    )
     
     
    Score measure ALL : =
    SUMX(Inventory, RELATED(Score[Score]))