Pivot Table Showing All Rows When Adding Calculated Measure

JohnLad85

New Member
Joined
Sep 22, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have created a pivot table that is built up of a number of tables in a data model. The N numbers are from a look up called WBS LookUp. The numbers beginning with 43000 are purchase orders from a PO LookUp. The PO's Raised, Invoices Paid and To be Delivered columns are all from different tables which are seperate spend reports and are linked by the look up tables. The Invoices Paid + To be Delivered is a custom measure.

I am trying to create a new measure that will show the status of a PO, whether it is closed or open based on if the value in To be Delivered is £0 or not, which works. However, when I add the measure to the pivot table it ignores the filtering / sorting created by pivot table rows (the WBS Look and PO Number) and creates a new row for every PO number in each field row and can be seen with the £0 values.

I have tried using calculate to filter by various tables and fields to solve the issue but I can't seem to resolve it.

Thanks in advance for any assistance.

1663859665932.png
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top