Using Data from Island Table in chart

[deniscamh]

Hi guys,

Here is my issue. Let say I have a table like this:

Table1:

Load * Inline [

Date,Player1, Player2, Score1, Score2

04/01/2018,1,2,6,4

04/01/2018,3,4,5,6

04/02/2018,2,3,5,6

04/02/2018,4,1,5,6];

Now I want to crate score for each player so I can do Sum(Score1) + Sum(Score2). As the same player can be Player1 or Player2  I have created an island table to have a list of players as dimension so it will work for both columns:

Players:

load distinct   Player1 as 'Players'

resident Master;

Concatenate (Players)

load distinct  Player2 as 'Players'

resident Tablle1;

Then I am using a P() function to create a formula:

sum({<[Player1] = P(Players)>}Score1)+sum({<[Player2] = P(Players)>}Score2). Now when I select a player it give me his total score BUT

When I add the 'Players' as a dimension to the bar chart the formula gives the total score (43) for each player unless I select a player.

My question is what I need to do so the chart will display the score per Player from the start with no selection are needed?

Hope the explanation makes sense.

Thanks

Denis

[sunny_talwar · Accepted solution]

Try this

Sum(If([Player1] = Players, Score1))

+

Sum(If([Player2] = Players, Score2))

Set analysis doesn't work because it is evaluated per chart and not per dimension. When you have nothing selected p() is taking all possible values within Players and summing it against an island table dimension which is why 43 repeated. Using if you can evaluate your condition per row.

[deniscamh]

Hi Sunny,

Nice to hear from you!

Thank you very much for solution and detailed explanation!

Your help and knowledge is much appreciated.

Thank you