- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
So I did some progess:
You are picking up the ratio 001/002 (task), but not the 407_ (project number)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
So I madesome progess:
It's picking up the 001/002 (task) relation but not the 407_ (project number)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
08-28-2024 08:47 AM | |||
10-05-2024 04:10 AM | |||
Anonymous
| 08-26-2022 08:17 AM | ||
11-08-2024 05:01 AM | |||
10-23-2024 02:17 AM |
User | Count |
---|---|
95 | |
71 | |
44 | |
38 | |
29 |
User | Count |
---|---|
156 | |
92 | |
61 | |
44 | |
42 |