Forum Discussion
Solved
Problems with pivot sum columns e file weight
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 becaus...
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.
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)] )
- 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 !!
