Pivot and number of lines displayed

[Tetonne]

Hello
I'm using pivot
and because data and filter can give different number of line I use this method

pivot based on data

under this pivot I set a table (part number of lines with every rows for example)

in this table each cell of one line
cell = one of the line in the pivot (ref to the pivot data)
ex A42=A24/A22...

my problem if I change the filter of my pivot the number of lines change and my 2nd table with calculation can be broken

how to solve this please?
thanks

I use pivot to have the needed data (row can be month)
the 2nd table is there to make my analyse from pivot data
ratio

 

[vletm]

Tetonne
Do You use Pivot or Power Pivot?
As You're MacOS-user, with that You can use Pivot.
Your how to solve this please?
Without a sample Excel-file,
which shows a sample data - Pivots - Tables - formulas as close as You're normally use and with expected results.

 

[Tetonne]

Hello vletm
thanks for your answer, her are more information and a sample file.

My need: stratify the data in a pivot table and in a 2nd time exploit these data by analyzing them (to build a dashboard) with data calculated from the stratified data.
My problem:
in global view (all business units) I can see all the data and mechanically all the possible lines present or not in the Business units and my analytical view is ok (left table)

if I update the filter by putting BU=2 for example
the pivot does not have the same number of lines and my analysis table becomes ko because the references of my formulas have become KO.
To visualize the differential, I duplicated my pivot on the right and in my analysis table everything is KO.
I thought of labeling the titles of the bottom table (analysis) with V-search... and fetch my data that way but it's crazy heavy.

by default I need the global view of all BU
but if I want to have a view at the Business unit level, all my analysis tables are knocked out

how to do in an analysis table to manipulate the data found and that it still works if in business unit view, one of the criteria is not found.

In short, my TCB displays all the possible data but displays 0 if not found in the business unit view.

 

[vletm]

Tetonne
In short
... You could sum those values per text - one sample ( check range I26:K34 ) .

 

[Tetonne]

huge thanks vletm