Problems with pivot sum columns e file weight

[studiofavari]

Good morning,

I am attaching an xls file that I built with macros and pivot table to the "Verify Maximum" sheet.
I have these 2 problems that I can't solve:

1) I had to compress the file because inexplicably it weighs 76 Mb and I don't understand why (it doesn't seem too complex)

2) The "Verification of ceilings" sheet contains a Pivot table that is generated through "table 3" located in the "Detail of ceilings" sheet. In the Pivot table I have created a column called "Deductible amount (calculated)" determined by a calculation that should reveal the minimum value of the values ​​between the columns "Average of maximum spending limit", "Sum of Total IVATO limit (Annex A point 13 Ministerial Decree 6/8/2020) "and" Sum of total cost of VAT ". The strange thing is that in row 16 it is evident that the calculation returns the correct value, that is € 200 placed in the "Average of maximum spending limit" column, but row 9 does not respond correctly to the same calculation, in fact it does not report the value of 24,000 € always on the "Average of maximum spending limit" column, but the value of € 30,181,452 .... which is by no means the lowest. Why does this happen if the calculation is the same and is defined for the pivot column in the "Pivot table analysis" section and then "fields, elements and sets" under "calculated field" identified by the "Deductible amount" field?

[#2 · Accepted solution]

@studiofavari 

1) The "used range" of most of the sheets was far too large. I deleted the unused rows and columns, and now the workbook is only 302 KB - see attached.

2) This is too complicated for me to investigate, sorry.

[#3]

@Hans Vogelaar grazie mille !!  thank you very much

Punto 1 risolto - Point one solved :)

Another help for resolve point 2

[#4]

@Hans Vogelaar how i can deleted the unused rows and columns? i tried but i was unsuccessful 

[#5]

@studiofavari 

Scroll down to the end of the data.

Select all rows from the first unused row down to the bottom of the worksheet.

On the home tab of the ribbon, select Delete > Delete Sheet Rows.

Scroll to the right to the end of the data.

Select all columns from the first unused column to the right edge of the worksheet.

On the home tab of the ribbon, select Delete > Delete Sheet Columns.

Repeat for each sheet.

Save, close and reopen the workbook.

[#6]

Unfortunately I tried to do so with a second file that is the same but with some data plus, however it does not lighten.

[#7]

@studiofavari 

I'd have to see the workbook.

[#8]

@Hans Vogelaar 

[#9]

@studiofavari 

[#10]

@studiofavari 

It was difficult to get the attachment to download, but it eventually worked.

I performed the steps I described in my previous reply, then saved the workbook. It went from over 79 megabytes to 630 kilobytes: less than 1% of the original size...

Remark: cell AN430 on the Dettaglio Massimali sheet contains a circular reference: the formula contains SUMIF functions that refer to column AN itself.

[#11]

@studiofavari 

As for the 2).

You added calculated field (aka another column to the table) which for each row calculates min value in 3 other columns. They are with red mark here

In PivotTable you aggregate this column by sum, which correctly returns 30,181.45 Occasionally it gives the same result as sum of Totale Costo.

For the next grouping

we have only one row to aggregate. Sum of it is equal average/min of first column.

I'm not sure what exactly you'd like to calculate. If min between totals for average and sums of other fields, that's if add data to data model and play with DAX measures using explicit measures for aggregation. Perhaps something like

Sum Totale limite IVATO:=SUM( Tabella3[Totale limite  IVATO (All. A punto 13 DM 6/8/2020)] )
Sum Totale Costo:=SUM( Tabella3[Totale costo IVATO] )
Average limite spesa massimo:=AVERAGE( [limite spesa massimo] )

Sum Importo detraibile:=MIN( MIN( [Average limite spesa massimo], [Sum Totale Costo] ), [Sum of Totale limite  IVATO (All. A punto 13 DM 6/8/2020)] )

[#12]

Hello Sergei Baklan,

I thank you infinitely for your availability and attention to my problem. I try to explain to you how the file that is linked to regulatory and economic checks for restructuring interventions of properties subject to tax incentives works.

In essence, the Italian law (which is unfortunately very complicated) provides for various tax incentives for carrying out restructuring interventions. The details of all types of tax incentives for each individual type of intervention are shown in the "Works" sheet.
Each intervention, linked to a tax incentive, can be characterized by 1 or 2 different economic limits:
1) MAXIMUM EXPENDITURE limit imposed and inclusive of VAT. (this limit is identified in columns C, J, M of the "Jobs" sheet based on the type of tax incentive); NB some interventions may be subject to all incentives, others not.
2) SUB-LIMIT of INTERVENTION EXPENSES determined on the basis of national price lists and inclusive of VAT. (this limit is identified in column E of the "Jobs" sheet).

The detail of all the activities to be carried out for the renovation of the property with the respective LIMIT AMOUNTS DEFINED BY THE NATIONAL PRICES (columns A to I) is included in the sheet "Maximum details".
The detail of all the activities to be carried out for the renovation of the property with the respective COSTS PROVISED BY THE COMPANIES THAT WILL DO THE WORKS (columns J up to N) is also included in the "Maximum detail" sheet.
Finally, in the "Maximum details" sheet I use columns from O to R (selecting mainly from the drop-down menu of column P) to identify the TYPE OF INTERVENTION in correspondence with the rows that I will then have to report in the PIVOT table of the "Verify ceilings" sheet .
Having calculated all the possible incentives for each line where the TYPE OF INTERVENTION is defined (columns from S to AX), the spreadsheet suggests which INCENTIVE is more favorable (columns from AY to BD), but allows me to choose which one to assign to the project (columns from BE to BJ) by acting on the drop-down menu in column BJ.
The bulk of the work is therefore carried out in the "Maximum detail" sheet where I identify all the activities, defining all the price limits imposed by the price lists, the type of intervention and the costs envisaged by the executing companies and finally choosing the type of tax incentive.

Having all these data, through the "check ceilings" sheet, it allows me to:
1) group all interventions by TYPE OF INCENTIVE
2) group all the activities by TYPE OF INTERVENTION

In this case, the verification of the economic portion subject to the incentive is performed for each TYPE OF INTERVENTION in this way in the pivot table (columns B, C and D):
1) I identify the "maximum spending limit" by type of intervention in column B
2) adding all the expenditure values obtained from the national price lists for each specific activity; I therefore define the overall spending limit of each individual activity on the basis of the national price lists, reporting it in column C.
3) adding up all the cost values proposed by the companies for each specific activity; I therefore define the overall cost projected by the companies for each individual activity on the basis of the national price lists, reporting it in column D.

In columns E and F I must therefore compare and summarize the following:

I add up all the SITE COSTS BY TYPE OF INTERVENTION and compare them with:
- The limit expenditure for the type of incentive;
- the expenditure limit value for the type of intervention;
IF the cost of production is lower than these two limits, the cost is entirely subject to an incentive (column E).
If it exceeds one of these two limit values, the lower limit value establishes the cost that can be considered subject to an incentive (column E) and the rest is not subject to an incentive (column F)

I add up all the COSTS FOR PROFESSIONAL EXPENSES BY TYPE OF INTERVENTION and compare them with:
- The limit expenditure for the type of incentive;
- the expenditure limit value for the type of intervention;
IF the cost of production is lower than these two limits, the cost is entirely subject to an incentive (column E).
If it exceeds one of these two limit values, the lower limit value establishes the cost that can be considered subject to an incentive (column E) and the rest is not subject to an incentive (column F)

FOR EACH TYPE OF INTERVENTION I must therefore add up the values calculated for SITE COSTS and the COSTS FOR PROFESSIONAL EXPENSES resulting in columns E and F to determine the total subject to incentives (column E) and the total is not subject to incentives (column F) . The lines with these totals are highlighted with a light blue color.

In cells B1 to I6, I then calculate the general summaries.

Unfortunately it is very messed up, but it is all a consequence of the Italian laws which are madness !!