Hi Mynda
I have built a Power Pivot data model at work which gives me the reports I need however on one of the pivot tables connected to my data model I have an issue with grouping. The row area of the PT field list is Customer names, the values area being the Total Invoice amount. My issue is we have two customers on our accounting software who are basically the same customer it is just they are in two different countries.
There was two invoices for one of them and a credit note for the other. Basically when I sort the pivot table in descending order for the Total Invoice Amount explicit measure I would like to group the two customers so that the net invoice amount is shown. It seems however that unlike a normal pivot whereby you right click and select Group, a Pivot based on a data model does not seem to allow this method of grouping? Is there another way I could do this?
Thanks
Debbie
Hi Debbie,
If you have one table in your model that contains your customer names and you're using that customer name field in the PivotTable, and don't also have the Country field in the PivotTable, then it will group those customer names automatically.
If it isn't doing this then it suggests the names are not identical. It may be something as innocuous as a trailing space after one of the names. If they are different, then you can either clean up your data so they're the same, or add a customer dimension table that maps the permutations of the names to a single common name and then use that name field in your PivotTable.
Mynda
