# (1) PIVOT TABLE NOT UPDATING (2) 2 TABLES PIVOT TABLE

#### shah0101

##### Board Regular
HI EXPERTS,

I HAVE TWO QUESTIONS:

1) MY TABLE IS UPDATING FINE BUT THE THE NUMBERS ON PIVOT TABLE NOT REFLECTING UPDATE (AFTER REFRESH). ANY SUGGESTIONS PLEASE?

2) ON ANOTHER REPORT I HAVE TWO TABLES OUT WHICH I HAVE CREATED ONE PIVOT TABLE. THERE ARE COUPLE OF CALCULATED FIELDS AS WELL, RELATIONSHIP BETWEEN TABLES LOOKS GOOD (AS ATTACHED IMAGE), REFLECTING BALANCES JUST FINE AS REQUIRED BUT THE PROBLEM IS THAT THE PIVOT TABLE IS NOT BRINGING UP ENTRIES FROM THOSE ENTRIES WHICH ARE NOT YET RELATED IN THE SECOND TABLE. I WANT ALL ENTRIES TO BE IN THE PIVOT TABLE. IS IT POSSIBLE OR AM I ASKING TOO MUCH?

THANKS FOR THE HELP IN ADVANCE.

CHEER!

#### Attachments

• 006.jpg
31.5 KB · Views: 10

#### Alex Blakenburg

##### Well-known Member
Is LC_21 Table2 ?

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### Alex Blakenburg

##### Well-known Member
If you take the fields that appear in both tables from Table 2 (LC_21) and have
turned on "items with No Data on Row" then you should get all items from Table 2 even if they are not in Table 1.

Can you elaborate and or show me a picture of what this means.
"BUT THIS IS REPEATING ALL ENTRIES FOR TABLE 2 AGAINST EACH DIMENSION ON TABLE 1."
.

#### shah0101

##### Board Regular
If you take the fields that appear in both tables from Table 2 (LC_21) and have
turned on "items with No Data on Row" then you should get all items from Table 2 even if they are not in Table 1.

Can you elaborate and or show me a picture of what this means.
"BUT THIS IS REPEATING ALL ENTRIES FOR TABLE 2 AGAINST EACH DIMENSION ON TABLE 1."
.

Apologies for getting back late, I was not around.

To make the query simpler, instead of referring to tables as Table 1 and Table 2, I'll refer to their names:

Table 1: INV_21
Table 2: LC_21

LC_21 is the table which needs to bring all entries, but it is bringing only those entries against which relationship (One to Many) is found on "INV_21"
INV_21 is the table for which I need only those entries which corresponds to LC_21 but Pivot is bringing all entries
So basically it should work the other way around.

I have tried "items with No Data on Row" but what is does is against each LC_21 entry it is repeating all entries of INV_21 which is of no use.

I am not sure if I am still able to explain the problem.

Cheers!

#### Attachments

• 001.jpg
33.3 KB · Views: 5

#### Alex Blakenburg

##### Well-known Member

I don't understand your pivot rows.
In rows I would expect to see.
• LC_21 > LC NO.
And the check box for "Show items with No Data on Row" should be ticked.

All the other items that are showing under rows seem to be values.

You are not showing giving me a full view of all the field names of both tables, so I am flying a bit blind here.
I assume all the other values under rows are coming from the INV_21 and this creating the multiplier effect that you don't want. Is this the case ?
Why do we have a values in the Row section ?

#### shah0101

##### Board Regular
I don't understand your pivot rows.
In rows I would expect to see.
• LC_21 > LC NO.
And the check box for "Show items with No Data on Row" should be ticked.

All the other items that are showing under rows seem to be values.

You are not showing giving me a full view of all the field names of both tables, so I am flying a bit blind here.
I assume all the other values under rows are coming from the INV_21 and this creating the multiplier effect that you don't want. Is this the case ?
Why do we have a values in the Row section ?

Please see attached is the full list of fields.

On Rows:
Yes, some of the fields like Amount, TTL Value, +/-, are value fields but I do no need the totals for that because these are simple fields brought up by Table: LC_21 without any calculation.

"LC Utilzied", is calculated field: =CALCULATE(sum(INV_21[NET VALUE]),FILTER(INV_21,INV_21[LC NO.]=LC_21[L/C NO.]))
"LC Balance", is calculated field: =LC_21[Sum of Total Available Value]-LC_21[LC Utilized]

And I do not need totals for these fields that is why we kept it in Rows.

"Invoice No." field is from "INV_21"

On Values:
All these are from "INV_21"

Looking forward to hear back.

#### Attachments

• 002.jpg
44.2 KB · Views: 3

#### Alex Blakenburg

##### Well-known Member

I don't think you can do it they way you are trying to do it.
The "Show all Rows without data" is more aimed at giving you a summarised report that consistently shows all the categories for use in a Report or Chart.
This would involve having any values data that come from the fact table (INV_21) appear in the values section of the Pivot.

Having invoice no from the fact table, not to mention other value fields including calculated fields, is what is going to blow out your matrix.
(The Join between the Data Model tables is essentially a cross join)

I think your best bet to get what you want is most likely to use Power Query.

#### shah0101

##### Board Regular
I don't think you can do it they way you are trying to do it.
The "Show all Rows without data" is more aimed at giving you a summarised report that consistently shows all the categories for use in a Report or Chart.
This would involve having any values data that come from the fact table (INV_21) appear in the values section of the Pivot.

Having invoice no from the fact table, not to mention other value fields including calculated fields, is what is going to blow out your matrix.
(The Join between the Data Model tables is essentially a cross join)

I think your best bet to get what you want is most likely to use Power Query.

Thanks Alex,

Actually all the data in both tables is extracted from various different files through Power Query already but the problem is with Pivot to organize the data in such a way the required report is generated.

I also tried all fields in "Rows" but its not working either.

May be I am not good enough to explain it properly, let me elaborate it again what I am trying to achieve:

Table 2: LC_21 (Contains the Field"L/C NO." which is the key and have one to many relationship with "Table 1: INV_21". This
Table 1: INV_21 (Contains multiple or in some cases no entries related to "LC_21: L/C NO."

So all I want the Pivot to bring all entries from "LC_21" and their related entries in "INV_21". Can you please guide me how should I organize the fields to get the desirable result please?

Cheers!

#### Attachments

• 004.jpg
37 KB · Views: 3
• 003.jpg
38.2 KB · Views: 3

#### Alex Blakenburg

##### Well-known Member
I also tried all fields in "Rows" but its not working either.
It is putting fields from INV_21 in "Rows" that is causing the issue in the first place.
I think your best chance of doing this particular report is to use Power Query. You can then easily ensure you get all the rows from the LC_21 table and you can control what appears on rows and what gets aggregated from the INV_21 table, without getting the Cross Join issue that you are experiencing using Pivots with the Data Model.

#### shah0101

##### Board Regular
It is putting fields from INV_21 in "Rows" that is causing the issue in the first place.
I think your best chance of doing this particular report is to use Power Query. You can then easily ensure you get all the rows from the LC_21 table and you can control what appears on rows and what gets aggregated from the INV_21 table, without getting the Cross Join issue that you are experiencing using Pivots with the Data Model.

Replies
1
Views
26
Replies
1
Views
35
Replies
0
Views
63
Replies
1
Views
339
Replies
6
Views
144

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,405
Messages
5,769,881
Members
425,578
Latest member
Ckrysa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back