Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone, I have a question about the limitations of the Matrix visual and sorting.
Currently I have a matrix that looks similar to this data:
Jan | Feb | Mar | Total | |
Microsoft | 10 | 20 | 30 | 60 |
Apple | 40 | 50 | 60 | 150 |
Amazon | 70 | 80 | 90 | 240 |
When you Expand All Down, it looks like this:
Jan | Feb | Mar | Total | |
Microsoft | 10 | 20 | 30 | 60 |
Bill Gates | 7 | 14 | 21 | 42 |
John Smith | 3 | 6 | 9 | 18 |
Apple | 40 | 50 | 60 | 150 |
Tim Cook | 28 | 35 | 42 | 105 |
Jony Ive | 12 | 15 | 18 | 45 |
Amazon | 70 | 80 | 90 | 240 |
Jeff Besoz | 49 | 56 | 63 | 168 |
Jane Doe | 21 | 24 | 27 | 72 |
If you then sort by Total, highest to lowest, it rearranges it to this:
Jan | Feb | Mar | Total | |
Amazon | 70 | 80 | 90 | 240 |
Jeff Besoz | 49 | 56 | 63 | 168 |
Jane Doe | 21 | 24 | 27 | 72 |
Apple | 40 | 50 | 60 | 150 |
Tim Cook | 28 | 35 | 42 | 105 |
Jony Ive | 12 | 15 | 18 | 45 |
Microsoft | 10 | 20 | 30 | 60 |
Bill Gates | 7 | 14 | 21 | 42 |
John Smith | 3 | 6 | 9 | 18 |
This sorts the company totals from highest to lowest, however I want the individual employee. Using the Advance to Next Hierarchy button instead of the Expand All Down, I am left with this (sorted highest to lowest on Total):
Jan | Feb | Mar | Total | |
Jeff Besoz | 49 | 56 | 63 | 168 |
Tim Cook | 28 | 35 | 42 | 105 |
Jane Doe | 21 | 24 | 27 | 72 |
Jony Ive | 12 | 15 | 18 | 45 |
Bill Gates | 7 | 14 | 21 | 42 |
John Smith | 3 | 6 | 9 | 18 |
This is how I want to view it, but I would also like the name of the company to tag along with the name of the person, like so:
Jan | Feb | Mar | Total | ||
Amazon | Jeff Besoz | 49 | 56 | 63 | 168 |
Apple | Tim Cook | 28 | 35 | 42 | 105 |
Amazon | Jane Doe | 21 | 24 | 27 | 72 |
Apple | Jony Ive | 12 | 15 | 18 | 45 |
Microsoft | Bill Gates | 7 | 14 | 21 | 42 |
Microsoft | John Smith | 3 | 6 | 9 | 18 |
Is this possible? For reference, data looks like this:
Name | Company | Month | Value |
Bill Gates | Microsoft | January | 3 |
Bill Gates | Microsoft | January | 4 |
Bill Gates | Microsoft | February | 10 |
Bill Gates | Microsoft | February | 3 |
Bill Gates | Microsoft | February | 1 |
Bill Gates | Microsoft | March | 7 |
Bill Gates | Microsoft | March | 6 |
Bill Gates | Microsoft | March | 7 |
Bill Gates | Microsoft | March | 1 |
John Smith | Microsoft | January | 3 |
John Smith | Microsoft | February | 6 |
John Smith | Microsoft | March | 9 |
Solved! Go to Solution.
What happens when you show Name as first column and Company as Second?
Also, Disable "stepped layout" and "+/-" options under Row Header.
If this helps, Mark it as a solution.
Kudos are good too
Man I was really overthinking this one... Putting names first and removing row subtotals got me exactly what I was looking for. Thank you!
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |