Hello, & thank you for any help you may offer! I tried to search the forum but could not find an answer to my issue.
I have a powerpivot table from my data model with 6 columns, & (currently) 2 filters (wanting to add a 3rd but need to figure out the relationships- different issue). 3 of the columns are Values (count, count, & sum). I can not get it to sort/filter/group the way I need it to. I also can't get a picture to post on this.
So I will try to explain what I have vs. what I need.
Pivottable:
Filters: Division & Name (Name pulled from different source in model. All other fields are pulled from another single source in the model)
Columns: Values
Rows: Unique ID, Name, & TM Inv
Values: Customer Count (count), Inv Count (count), & Total Inv $ (Sum)
There are multiple Uniq ID's (Column B) that have more than 1 Name (so more than 1 row), but never the same name more than once on a single Uniq ID. Some have a Y in TM Inv field (Column D) & some don't. Each Uniq ID has a Customer Count, Inv Count, & Total Inv $ in the Value columns E, F, & G.
I want the default sort to be: Unique ID by Total Inv $ no matter how it is expanded/collapsed/ or filtered.
Right now when expanded, it is sorted as: Uniq ID and then by Total Inv $.
With the name field after the Uniq ID field, the Total Inv $'s are skewed when all fields are collapsed, because more than 1 name can be associated to a Uniq ID. So when collapsed, the repeated counts shown on each Name, now sum to give new totals when collapsed. The Uniq ID only has one Customer count, Inv Count, & Inv Total, no matter how many names. It should show the same 3 totals on each name. The Counts are tied to the Unique ID. The grouping feature is grayed out on all fields, so that is not an option.
So how do I set this up to sort the way I need it to? It currently filters fine, but I can't figure out how to sort when collapsed as Unique ID by Total Inv $, without the Uniq ID's that have multiple names, adding the Customer Counts, Inv Counts, & Total Inv $ fields together when collapsed. When collapsed, I need the counts & totals to be the same, not summed.
Examples:
With all fields expanded, even those with multiple names, everything shows correctly with the same Cust Count, Inv Count, & Total Inv $ for each Name & Uniq ID:
Unique ID: Name: TM Inv: Cust Count: Inv Count: Total Inv $:
123456789 Bob Smith Y 6 3 $430.49
Jane Jones Y 6 3 $430.49
987654321 Bob Smith 3 1 $399.62
But when collapsed it incorrectly shows this:
Unique ID: Name: TM Inv: Cust Count: Inv Count: Total Inv $:
123456789 12 6 $860.98
987654321 3 1 $399.62
I need it to show this when collapsed:
Unique ID: Name: TM Inv: Cust Count: Inv Count: Total Inv $:
123456789 6 3 $430.49
987654321 3 1 $399.62
I hope this makes some kind of sense. If not, I'm sorry to have wasted your time.
Thank you for any help!!
I have a powerpivot table from my data model with 6 columns, & (currently) 2 filters (wanting to add a 3rd but need to figure out the relationships- different issue). 3 of the columns are Values (count, count, & sum). I can not get it to sort/filter/group the way I need it to. I also can't get a picture to post on this.
So I will try to explain what I have vs. what I need.
Pivottable:
Filters: Division & Name (Name pulled from different source in model. All other fields are pulled from another single source in the model)
Columns: Values
Rows: Unique ID, Name, & TM Inv
Values: Customer Count (count), Inv Count (count), & Total Inv $ (Sum)
There are multiple Uniq ID's (Column B) that have more than 1 Name (so more than 1 row), but never the same name more than once on a single Uniq ID. Some have a Y in TM Inv field (Column D) & some don't. Each Uniq ID has a Customer Count, Inv Count, & Total Inv $ in the Value columns E, F, & G.
I want the default sort to be: Unique ID by Total Inv $ no matter how it is expanded/collapsed/ or filtered.
Right now when expanded, it is sorted as: Uniq ID and then by Total Inv $.
With the name field after the Uniq ID field, the Total Inv $'s are skewed when all fields are collapsed, because more than 1 name can be associated to a Uniq ID. So when collapsed, the repeated counts shown on each Name, now sum to give new totals when collapsed. The Uniq ID only has one Customer count, Inv Count, & Inv Total, no matter how many names. It should show the same 3 totals on each name. The Counts are tied to the Unique ID. The grouping feature is grayed out on all fields, so that is not an option.
So how do I set this up to sort the way I need it to? It currently filters fine, but I can't figure out how to sort when collapsed as Unique ID by Total Inv $, without the Uniq ID's that have multiple names, adding the Customer Counts, Inv Counts, & Total Inv $ fields together when collapsed. When collapsed, I need the counts & totals to be the same, not summed.
Examples:
With all fields expanded, even those with multiple names, everything shows correctly with the same Cust Count, Inv Count, & Total Inv $ for each Name & Uniq ID:
Unique ID: Name: TM Inv: Cust Count: Inv Count: Total Inv $:
123456789 Bob Smith Y 6 3 $430.49
Jane Jones Y 6 3 $430.49
987654321 Bob Smith 3 1 $399.62
But when collapsed it incorrectly shows this:
Unique ID: Name: TM Inv: Cust Count: Inv Count: Total Inv $:
123456789 12 6 $860.98
987654321 3 1 $399.62
I need it to show this when collapsed:
Unique ID: Name: TM Inv: Cust Count: Inv Count: Total Inv $:
123456789 6 3 $430.49
987654321 3 1 $399.62
I hope this makes some kind of sense. If not, I'm sorry to have wasted your time.
Thank you for any help!!
