Can't hide zero value calculated items in a pivot table

Hello,
I need help to find a way to hide zero value calculated items in a pivot table.
So far I have tried a number of tricks at no result.
I have a database for CO2 tons emissions for 2021 for a number of scopes and subcategories for some countries and locations.
I need to compute 2025 estimations by applying a coefficient to some 2021 values.
I have a done a pivot per rows = Category / subcategory. Columns = Years, E values = Sum of tCO2eq.
I'm adding two calculated items to the Years field.
The first is "Coeff" and its formula is "= 1"
The second is "2025" and its formula is "= '2021' * Coeff".

As soon as I enter the Coeff calculated item a number of additional rows appear for all categories with "0" values, whereas before only the rows with values would appear.
I can't find a way to hide the zero values lines.

Can someone help me ?

Thanks,
-Pierre

30/08/2022 update: the "Coeff" value need to be set on a line per (non zero) line basis in the Pivot (that's why I used a calculated item).
31/08/2022 update: the "Coeff" value needs to be set on PivotTable row, not per line within Database. e.g. I want to set a 0,6 coeff value on the "Purchased goods and services" row for "COmpany". THat would calculate 2025 CO2 emissions as reduced by 40% and give 442 817 (that is 40% of 2021 emissions as 738 028).

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Please find requested attachment.
Thanks

UPdate: sorry attachment was wrong. I fiexed and the BEFORE and AFTER sheets are now correct. What I want to hide are all the liens with nothing for 2021, "1" for Coeff and "0" for 2025. I can't find how to do it.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As you are new here, I shall do it for you this time: https://www.mrexcel.com/board/thread...table.1214790/)

Many thanks for your message. but, Oops ! Apologies ! I'm lost here...
I have posted yesterday to excelforum forum and today on MrExcel.
I thought they were different forums and there was no issue to post to several "places" to get more chance to have answer.
Am I wrong ? If yes, again apologies. I just want to find a solution.
Are these two forums linked or separated ?
If separated, I guess I should have informed Excelforum that I had raised the question on MrExcel too ?
Please clarify.
Thanks and best regards,
-Pierre

Note: in the MrExcel post, I've not been able to add the attachment sample XL file.

This may be a work around.
On the Database After sheet cell L1 displays the Coeff
Cells A307:I611 are added by formula (i.e. =A2)
Cells G307:G611 are populated with 2025
Cells H307:H611 are populated using: =H2*Coeff
The pivot table on Sheet1 mimics the pivot on the AFTER sheet with the exception of the Coeff field, which again will cause all zero values to be displayed.
Note that I attempted 'fixing' the pivot table on the AFTER sheet by simply removing the Coeff field but it didn't work.
Let us know if you have any questions.

@josephteh, good solution.

I understood you can you this using Calculated items.

I don't believe that is the case, however if you can show us a source for that then I'll take a look.
I feel that in order to have a coefficient per line that it would need to be added to the source data.
In the attached file column I has some random coefficients.
On the pivot table the 2025 column is produced using a calculated field: =teqCO2 *Coeff
Let us know if you have any questions.

Originally Posted by JeteMc

I don't believe that is the case, however if you can show us a source for that then I'll take a look.
I feel that in order to have a coefficient per line that it would need to be added to the source data.
In the attached file column I has some random coefficients.
On the pivot table the 2025 column is produced using a calculated field: =teqCO2 *Coeff
Let us know if you have any questions.

Okay, thanks a lot for trying. I need to precise one more thing, I don't want to set a coeff per DB line, but on a whole category (that is on a PivotTable non-zero row).
I'll try to post a sample.
See initial post for update and example. and see attached file

Someone may be able to accomplish this using Power Pivot. I tried a couple of ways but got the unwanted zero value lines in the resulting pivot table so I resorted to the following:
1. Set us a table of coefficients by subcategories
Note that diesel is listed twice but the first occurrence has an extra space which needs to be removed in the DB table and then tblCoeff.
2. Populated a column of coefficients in the DB table using: =INDEX(tblCoeff[Coeff],MATCH(F2,tblCoeff[SubCategory],0))
3. Put the Coeff field in the Values area and summarized the values by average so that the subtotal rows would display correctly
4. Added the following Calculated Field (2025): =teqCO2 *Coeff
Let us know if you have any questions.

Originally Posted by JeteMc

Someone may be able to accomplish this using Power Pivot. I tried a couple of ways but got the unwanted zero value lines in the resulting pivot table so I resorted to the following:
1. Set us a table of coefficients by subcategories
Note that diesel is listed twice but the first occurrence has an extra space which needs to be removed in the DB table and then tblCoeff.
2. Populated a column of coefficients in the DB table using: =INDEX(tblCoeff[Coeff],MATCH(F2,tblCoeff[SubCategory],0))
3. Put the Coeff field in the Values area and summarized the values by average so that the subtotal rows would display correctly
4. Added the following Calculated Field (2025): =teqCO2 *Coeff
Let us know if you have any questions.

Thanks for your answer.
I'll look into it.
However note that coefficients may be different for different countries in the same category.

01/09/2022 update: Thanks but your solution doesn't allow to set different coefficients to various location in the same category. See updated note on original post with example.