Matrix Summing Totals

[robandemmy]

Hello,

I am having trouble getting a visualization to work properly. I have two tables (approved and spent) that each have "project number" rows and "task" columns. Can use the matrix visual fine for one table but when I try to add the second, it sums the values instead of showing vaules for each task:

Approved column showing total instead of individual values

Am I doing something wrong, or is what I am attempting not possible? I can combine these table in excel and pivot them but they're large and annoying to do so.

Thanks,

Rob

[Syndicate_Admin]

So I did some progess:

Now displays the total of all actual ITD data, not those related to tasks 001 and 002This is the relationship

You are picking up the ratio 001/002 (task), but not the 407_ (project number)

[robandemmy]

So I madesome progess:

Now shows total for all ITD Actuals, not the ones related to the 001 and 002 tasksThis is the relationship

It's picking up the 001/002 (task) relation but not the 407_ (project number)

[Greg_Deckler · Accepted solution]

@robandemmy  OK so now you have a many-to-many which tends to be a bad thing. Generally, to fix this and make it work correctly, you need to create a bridge table like:

Table = DISTINCT('PCSData'[Task])

You relate this to both of your tables and use the Task column in this bridge table in your visualizations.

[amitchandak]

@robandemmy , seem like you using if in some calculation if so use the filter

Or try to have calculation like this

sumx(summarize(Table, Table[project Number],Table[project line],"_1",[measure]),[_1])

//Group by which I add are the rows in you matrix, can not see the all name

[Greg_Deckler]

Generally when you see things like that their is a bad or missing relationship between 2 tables.

[parry2k]

@robandemmy not sure how the relationship is set up between these tables, the recommendation would be to have separate project and task table and have a relationship with both these tables and then use these new dimension tables in the matrix visuals.