Forum Discussion
Matrix Summing Totals
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
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.
6 Replies
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.
- Generally when you see things like that their is a bad or missing relationship between 2 tables.
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
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)
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.
So I did some progess:
Now displays the total of all actual ITD data, not those related to tasks 001 and 002
This is the relationship
You are picking up the ratio 001/002 (task), but not the 407_ (project number)
